Simple Excel Logic Turned Challenging To Recreate in PowerPivot

Anything related to PowerPivot and DAX Formuale
sheidari
Posts: 9
Joined: Fri Mar 31, 2017 2:04 am

Simple Excel Logic Turned Challenging To Recreate in PowerPivot

Postby sheidari » Fri Mar 31, 2017 4:22 am

I have an FTE model that I built for a client. The client would like to track the ins and outs of each employee for FY17 and FY18.

Let's use John Doe as an example:

- starts work as a new hire in Oct 2016
- goes on a leave of absence (LOA) in Feb 2016
- returns from LOA in May 2016

The section highlighted with a green border tracks the month-over-month (MoM) changes. The section below that (highlighted in blue) tracks the events that occur. In John's example, there were 3 changes (or events) that occurred. The event triggers help me match the change type to each month.

Chg 1: New Hire In
Chg 2: LOA Out
Chg 3: LOA In

The Problem:

The section at the bottom, highlighted in red (FY17 Summary of Changes by FTE Type), tracks the MoM changes by FTE type. This is what I'm attempting to recreate in PowerPivot. I'm struggling on how to model out the logic so that I can get the same reporting capabilities. I've attached a sample of my FTE tool to help.

What I've done so far:

- Uploaded all tables to Power Query & PowerPivot
- Completed data manipulation in Power Query

Any help will be greatly appreciated!
Attachments
Many2Many.xlsx
(890.8 KiB) Downloaded 26 times

sheidari
Posts: 9
Joined: Fri Mar 31, 2017 2:04 am

Re: Simple Excel Logic Turned Challenging To Recreate in PowerPivot

Postby sheidari » Mon Apr 03, 2017 11:51 pm

anyone? :D

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Simple Excel Logic Turned Challenging To Recreate in PowerPivot

Postby MattAllington » Fri Apr 07, 2017 9:22 am

I will try to help you (when I can). The first thing I suggest you do is read my best practice article here

http://exceleratorbi.com.au/best-practi ... -power-bi/

I am looking at your tables but I have no idea what they are. eg what is Tbl_ETrigger_FY17 ? Best practice is to use a single noun (or short phrase) that clearly describes the table. Also get rid of the Tbl_ prefix as this doesn't add any value and makes everything harder to understand.

How do these tables related to each other?
Tbl_ETrigger_FY17
Tbl_Senior_FY18
Tbl_ETrigger_FY18

Can you describe the transactional data in your tables - I don't understand what this data is or how it is generated.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

sheidari
Posts: 9
Joined: Fri Mar 31, 2017 2:04 am

Re: Simple Excel Logic Turned Challenging To Recreate in PowerPivot

Postby sheidari » Thu Apr 20, 2017 5:59 am

MattAllington wrote:I will try to help you (when I can). The first thing I suggest you do is read my best practice article here

http://exceleratorbi.com.au/best-practi ... -power-bi/

I am looking at your tables but I have no idea what they are. eg what is Tbl_ETrigger_FY17 ? Best practice is to use a single noun (or short phrase) that clearly describes the table. Also get rid of the Tbl_ prefix as this doesn't add any value and makes everything harder to understand.

How do these tables related to each other?
Tbl_ETrigger_FY17
Tbl_Senior_FY18
Tbl_ETrigger_FY18

Can you describe the transactional data in your tables - I don't understand what this data is or how it is generated.


Thanks, Matt. I've looked over your article and found great value in a lot of the things you talk about and will certainly adjust my modelling going forward.

I appreciate you taking the time to look at this. So instead of me having to explain all of the tables above and how they related to one another, let's keep things simple and remove the above tables out of the equation. So if we do so, we're left with the "Tbl_Senior". This is an input table where users will add the ins & outs of each FTE for the current fiscal year. As you can see, each employee can have up to 8 changes (Chg 1-8) occur in the year. One example is below:

John Doe

Chg 1: New Hire In
Chg 2: LOA Out
Chg 3: LOA In

The section below that is called FY17 MoM Changes by FTE Type. This tracks the MoM changes by FTE type. This is what I'm attempting to recreate in PowerPivot. How would you go about modelling this logic out in PowerPivot in order to recreate the FY17 MoM Changes by FTE Type report in a pivot table?

I've attached a revised sample that removed most of the unnecessary tables in the original file.
Attachments
Many2Many Revised.xlsx
(552.44 KiB) Downloaded 20 times

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Simple Excel Logic Turned Challenging To Recreate in PowerPivot

Postby MattAllington » Thu Apr 20, 2017 6:57 am

So are you just trying to count how many FTEs you have each month (simple count if they are there) and then work it the change vs prior month? What happens if someone is there for 2 days in the month? Are they counted as there or not, or are they counted based on there % of the month there? What happens if the same person is ther on 1 -7 of the month and 25-31 of the month?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

sheidari
Posts: 9
Joined: Fri Mar 31, 2017 2:04 am

Re: Simple Excel Logic Turned Challenging To Recreate in PowerPivot

Postby sheidari » Thu Apr 20, 2017 7:11 am

Yes. The count will be based on the # of hours they worked in that given month. So the FTE count could be anywhere between 0-1.

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Simple Excel Logic Turned Challenging To Recreate in PowerPivot

Postby MattAllington » Thu Apr 20, 2017 7:38 am

I've only looked at the file via iPad, but I don't see the data needed to do what you say. There are change events but I don't see dates of these events. How do you know what is a working day, non working day?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

sheidari
Posts: 9
Joined: Fri Mar 31, 2017 2:04 am

Re: Simple Excel Logic Turned Challenging To Recreate in PowerPivot

Postby sheidari » Fri Apr 21, 2017 1:29 am

assume that the user will calculate the FTE by month outside of this tool and then plug in their FTE count for each month in here.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest