DAX/PowerPivot around isolating two date *ranges*

[analyst44]

Hello!

Been a long time since I've posted/commented here. Such a great community. I'm so glad to see it's still going strong and has added MS's PowerBI/PowerPivot to the mix.

I am a noob in that regard, having slowly worked through Rob Collie's DAX Formulas for PowerPivot and other online training (Chandoo, ExcelIsFun, etc).

I have a very distinct issue and I can't seem to figure out how to use PowerPivot to make it happen. This is important because if I don't, I will lose a lot of powerful functionality downstream.

First an older file with the exact same issue in Excel:

Basically, what I am doing is trying to discern the Available hours for any given chosen week (Rows 1 and 2), based on whether the person was hired and/or fired.

• So, if they weren't hired before the week in question, 0 available hours.
• If they where hired before the week started and did not leave/term until after that week, then all available hours (40)
• If they were hired before the week started and left/termed that week, find the networkdays up until that point and determine available hours (8*number of days)

Back then, I knew this was kludgy when I made it originally, but given the structure of the rest of the file, it worked and allowed me to do what I needed to do.

Now, I have a need to more dynamically do this (the original approach was dynamic for a set amount of weeks) given my PowerPivot approach and for much more flexibility.

I have a raw data table that has hours worked by day.
I have a calendar/dates table that has all the pertinent date breakdowns
I have a StaffList table that has each person's start and stop date (if applicable)

From here, I'm trying to create a measure to figure out precisely what I did above, but am totally stuck. I don't know how to relate the Start/End dates with the Week Start/Week End dates with the person's hours for the week. The relationships are fCradle <-> dDate based on date; fCradle <-> dStaffList based on Full Name.

This seems doable, but I can't crack it. Is there someone who can provide some direction on whether this is possible and, if so, how to get started? I've been at this for hours and hours over a few days, but no luck.

Based on the stuff I've read, I thought maybe a Disconnected Table that uses Week Selection (Week Number?) as a slicer might be one step, but I still need to relate each week back to the Start/End date for the employee so I am flummoxed.


Thank you so much for reading. I know this longer than a typical question and a bigger ask.

 

[SimonNU]

Basically, what I am doing is trying to discern the Available hours for any given chosen week (Rows 1 and 2), based on whether the person was hired and/or fired.

• So, if they weren't hired before the week in question, 0 available hours.
• If they where hired before the week started and did not leave/term until after that week, then all available hours (40)
• If they were hired before the week started and left/termed that week, find the networkdays up until that point and determine available hours (8*number of days)

Sorry I don't have time to thoroughly read your post, I have a spare 10 minute break at work

With respect to the quote above, Available Hours equals the count of weekdays * 8? This should account for all three points above.

More information may be required though - in the Payroll world there are many definitions of Available Hours.

• What about time off? e.g. it may or may not include holidays, sick leave, study leave and various combinations thereof.
• Also, what if I were terminated on Friday but stopped working on Wednesday? Are my available hours 40 or 24? This one affects the implementation because in the first instance you require the use of the termination date column however the latter you do not because you could just use a count of days (with respect to the point).

Worst case scenario - I would look into the implementation of Available Hours:= COUNTROWS( dDate or fCradle ) * 8. Since you are learning, I would suggest adding the following calculated column to your fact table to help you think about the matter:

= CALCULATE( COUNTROWS( fCradle), ALLEXCEPT( fCradle, dStaffList[employeeNumber], dDate[Week Number] ) ) * 8

Lastly, think about wrapping a FILTER around the ALLEXCEPT where the date <= termination date and >= hire date.

 

[analyst44]

Sorry I don't have time to thoroughly read your post, I have a spare 10 minute break at work

With respect to the quote above, Available Hours equals the count of weekdays * 8? This should account for all three points above.

More information may be required though - in the Payroll world there are many definitions of Available Hours.

• What about time off? e.g. it may or may not include holidays, sick leave, study leave and various combinations thereof.
• Also, what if I were terminated on Friday but stopped working on Wednesday? Are my available hours 40 or 24? This one affects the implementation because in the first instance you require the use of the termination date column however the latter you do not because you could just use a count of days (with respect to the point).

Worst case scenario - I would look into the implementation of Available Hours:= COUNTROWS( dDate or fCradle ) * 8. Since you are learning, I would suggest adding the following calculated column to your fact table to help you think about the matter:

= CALCULATE( COUNTROWS( fCradle), ALLEXCEPT( fCradle, dStaffList[employeeNumber], dDate[Week Number] ) ) * 8

Lastly, think about wrapping a FILTER around the ALLEXCEPT where the date <= termination date and >= hire date.

Thank you for the response, SimonNU! I really appreciate it!

There is going to be an implementation of off-time eventually, which is something I've done before. In Excel, this was very easy: I just added a dynamic named range that calculates the paid time off (PTO) by person, by week, in a separate table and then just subtracts it out very easily (index/match using the named range). Doing that in PowerPivot will be a challenge, but, for now, it's not spec'd and not likely to be in the near future. I do have my eye on it knowing it'll eventually be there.

The second bullet is what I had mentioned above in terms of being hired or fired with not a full week. That's where the networkdays function would be used in Excel to have those irregular hours (8,16,24,32).

I am definitely going to give your method a try. For now, I'll share what I did do to get myself through it. I would very much like to figure out a way to not use this "hybrid" method given it feels clumsy and perhaps unstable (from a refresh standpoint).

I created a pivot table that combines fields from multiple tables:

In order: dStaffList, dDate, dStaffList, dStaffList, dDate, dDate, fCradle

From there, I calculated Available hours by hand in a new table inside Excel (dynamically changes its range based on the size of the pivot table using VBA):

That table, fTrueAvailableHours was then added to the Data Model. I know, it's kinda weird to circularly do it this way!

The new diagram looks like this:

From there, I did my measures, the latter of which is a many-to-many calc:

Total Available Hours:=sum(fTrueAvailableHours[True Available hours])
Total Available Hours M2M:=calculate([Total Available Hours], dWeekBreakdown, dStaffList)

And from there I could create a new pivot that allows me to marry fCradle to fTrueAvailableHours using dDate, dStaffList, etc. and that gives side-by-side measures of information from both tables and can use slicers and all the functionality I need for now.

Percent Utilization:=IFERROR([Total Cradle Hours] / [Total Available Hours M2M], BLANK())

I hope to re-do this at some point so that it's "cleaner" but I needed to make some progress on it for my client (there's a lot more that's going to build on this in the future) and so this is, hopefully, a good go-between. Took me a lot of hours of racking my brain to get here and it was kinda disappointing.

 

[SimonNU]

Oh boy! I did almost the exact same thing you're currently doing about 3 years ago when I first got into DAX. Similarly, my first attempts were also on a staff utilization report however I also had to tie it to a general ledger for P&L-based operational metrics, e.g. Rev per FTE, which SBUs had the lowest utilization, $ lost by unbilled hours etc...

Your implementation suggests that you've got a keen eye for Excel but haven't fully jumped onto the dimensional data modelling train of thought yet. Rest assured, what you're trying to accomplish is well within the realm of Power Pivot's capabilities (and you certainly don't need a m2m setup for something like this!).

Maybe I'm missing something here but are you working with daily or weekly data in your fact table? I assumed daily because I saw Day Name in your dDate table. If it's weekly then you can't do COUNTROWS() because there'd be 1 row per week instead of 5 rows for an employee that worked mon-fri in a week.

Anyhoo, my biggest piece of advice: start with calculated columns and never jump straight into a measure unless you're experienced. Once your calculated columns are setup and reconciled only then should you replace them with measures. In practice, therefore, I suggest using RELATED to denormalize all of the required dimension columns for your calculation into your fact table and then perform your calculation. Once that it calculating correctly then you can perform cleanup by deleting all related's and turning your calculation into a measure (but obviously make sure the measure still reconciles to the calculated column and operates correctly at different granulates in a pivot table before cleaning up).

I note that you have read Rob Collie's book. However, this is undeniably the DAX bible: https://www.amazon.ca/Definitive-Gu...g_2?ie=UTF8&psc=1&refRID=JNH7A8QGAPMB0FF7Y6M8

 

[analyst44]

Oh boy! I did almost the exact same thing you're currently doing about 3 years ago when I first got into DAX. Similarly, my first attempts were also on a staff utilization report however I also had to tie it to a general ledger for P&L-based operational metrics, e.g. Rev per FTE, which SBUs had the lowest utilization, $ lost by unbilled hours etc...

Your implementation suggests that you've got a keen eye for Excel but haven't fully jumped onto the dimensional data modelling train of thought yet. Rest assured, what you're trying to accomplish is well within the realm of Power Pivot's capabilities (and you certainly don't need a m2m setup for something like this!).

Maybe I'm missing something here but are you working with daily or weekly data in your fact table? I assumed daily because I saw Day Name in your dDate table. If it's weekly then you can't do COUNTROWS() because there'd be 1 row per week instead of 5 rows for an employee that worked mon-fri in a week.

Anyhoo, my biggest piece of advice: start with calculated columns and never jump straight into a measure unless you're experienced. Once your calculated columns are setup and reconciled only then should you replace them with measures. In practice, therefore, I suggest using RELATED to denormalize all of the required dimension columns for your calculation into your fact table and then perform your calculation. Once that it calculating correctly then you can perform cleanup by deleting all related's and turning your calculation into a measure (but obviously make sure the measure still reconciles to the calculated column and operates correctly at different granulates in a pivot table before cleaning up).

I note that you have read Rob Collie's book. However, this is undeniably the DAX bible: https://www.amazon.ca/Definitive-Gu...g_2?ie=UTF8&psc=1&refRID=JNH7A8QGAPMB0FF7Y6M8

Sorry for the lateness of the response and thank you for the follow up. I am definitely way way more comfortable with Excel than PowerPivot. I'd consider myself an Advanced+ user of Excel. This is the first time I've ever used PowerPivot for anything serious so I am on a steep learning curve. I know what I want to do is likely possible in PowerPivot in terms of making this formula in Excel:

<week_start),0,if(and(hire_date<=week_start,term_date<week_end),networkdays(week_start,term_date)*8,error.type(#value!))))))[ code][="" code]
IF(AND(Hire_Date<=Week_Start, OR(Term_Date=0,Term_Date>Week_End)), Standard_Available_Hours,IF(AND(Hire_Date>Week_Start,Hire_Date<=Week_End, OR(Term_Date=0,Term_Date>=Week_End)), NETWORKDAYS(Hire_Date,Week_End)*8, IF(Hire_Date>Week_End,0, IF(AND(Hire_Date<=Week_Start,Term_Date

....available inside PowerPivot. The reason I came here was to learn how to translate the above into DAX somehow, but I didn't get very far before I decided to circumvent it per my post above given I just don't have the syntax down yet and maybe it's too big of an ask here. It's more difficult than Excel to frame questions here when it's across a schema/set of tables.

The transactions for time-tracked hours are at the day level (in fCradle) and there can be multiple transactions per day per person. The rollups will all be at the week level or higher (month, quarters, years), so that's why True Available hours is a weekly calc.

Thanks so much for the advice about how to learn DAX. It's interesting that you suggest calculated columns as the way to familiarize myself. Everything I've read online/blogs and via Rob Collie has deeply discouraged that and specifically states to use calculated columns only when necessary and under specific circumstances. So all the hours I've put in so far have been around the measures/calculated fields.

I have no problem using Calculated Columns for this and started to go down that route when I couldn't figure out a way to do it otherwise, but I couldn't even figure out the above translation into DAX using calculated columns so just did what I could to make it work. What you said about using RELATED to denormalize makes practical sense, but it's kinda lost on me at this point given it's so new. I'll dig into it. I'm just trying to make an efficient first model and continue my learning without getting buried. This singular calculation (in the code window) is my one hang-up so far.

This is an important learning exercise for me given there are going to be a number of things down the road that either utilize this model or something similar (client profitability, etc.).

I've heard of those guys that wrote that book and been to their site, but didn't know they wrote a book. I'll have to check it out. They seem to be very highly thought of in the PP/SQL community. Wish I would have know that to start if it would have helped me with this!

Thanks again for the insights. If you have any advice for how to parse the above calculation inside of PP, I'd be very appreciative. I'm very committed to learning DAX and the PowerBI suite.</week_start),0,if(and(hire_date<=week_start,term_date<week_end),networkdays(week_start,term_date)*8,error.type(#value!))))))[>

 

[analyst44]

Sorry! That formula got jacked when I was doing Find and Replace to make it more readable. Here it is:

=IF(AND(Hire_Date<=Week_Start,OR(Term_Date=0,Term_Date>Week_End)),Standard_Available_Hours,IF(AND(Hire_Date>Week_Start,Hire_Date<=Week_End,OR(Term_Date=0,Term_Date>=Week_End)),NETWORKDAYS(Hire_Date,Week_End)*8,IF(Hire_Date>Week_End,0,IF(AND(Hire_Date<=Week_Start,Term_Date<Week_Start),0,IF(AND(Hire_Date<=Week_Start,Term_Date<Week_End),NETWORKDAYS(Week_Start,Term_Date)*8,ERROR.TYPE(#VALUE!))))))

 

[analyst44]

Something is odd with this formula. Doesn't work even when I use code tags. Here's just an image. Not very efficient but reasonably easy to read without too many consolidations:

Sorry for the multiple posts on the same thing. I don't know why it keeps truncating.

 

[SimonNU]

Sorry for the late reply, I don't visit these forums often.

I've developed a simple solution for you located here.

It requires Power Pivot for Excel 2013 or greater. If you want to refresh the data then it requires Power Query.

I tried to develop it in a simple manner. It's not an efficient solution but I hope it will demonstrate how you can compartmentalize your problem into manageable parts.

Overview:

I created three measures based on your IF statement:

1. Termination or Hire Week?
2. Not Employed?
3. Count of Days

These then mashed them into this formula:

[COLOR=#0070FF]SUMX[/COLOR][COLOR=#969696] ([/COLOR]
[COLOR=#0070FF]SUMMARIZE[/COLOR][COLOR=#969696] ([/COLOR] fCradle, dStaffList[Employee Code], dDate[YYYYWW] [COLOR=#969696])[/COLOR],
[COLOR=#0070FF]SWITCH[/COLOR][COLOR=#969696] ( [/COLOR][COLOR=#0070FF]TRUE[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR],
[Termination or Hire Week?] = [COLOR=#0070FF]TRUE[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR], [Count of Days] * [COLOR=#EE7F18]8 [/COLOR]+ [COLOR=#EE7F18]0[/COLOR],
[Not Employed?] = [COLOR=#0070FF]TRUE[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR], [COLOR=#EE7F18]0[/COLOR],
[COLOR=#EE7F18]40
[/COLOR][COLOR=#969696])[/COLOR][COLOR=#969696])[/COLOR]

As mentioned, there are potentially many nuances with this type of calculation, e.g. holidays, termination dates on weekends etc... My example is very basic and doesn't account for these.

There are also many other ways to approach this. My advice is to rethink your problem and consider calculating it on a daily basis (if possible) instead of your requirements which are weekly. This is how Power Pivot models should be designed, i.e. account for all possible dimensions in the model. This almost always means calculating at the lowest granularity.

 

[analyst44]

Sorry for the late reply, I don't visit these forums often.

I've developed a simple solution for you located here.

It requires Power Pivot for Excel 2013 or greater. If you want to refresh the data then it requires Power Query.

I tried to develop it in a simple manner. It's not an efficient solution but I hope it will demonstrate how you can compartmentalize your problem into manageable parts.

Overview:

I created three measures based on your IF statement:

1. Termination or Hire Week?
2. Not Employed?
3. Count of Days

These then mashed them into this formula:

[COLOR=#0070FF]SUMX[/COLOR][COLOR=#969696] ([/COLOR]
[COLOR=#0070FF]SUMMARIZE[/COLOR][COLOR=#969696] ([/COLOR] fCradle, dStaffList[Employee Code], dDate[YYYYWW] [COLOR=#969696])[/COLOR],
[COLOR=#0070FF]SWITCH[/COLOR][COLOR=#969696] ( [/COLOR][COLOR=#0070FF]TRUE[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR],
[Termination or Hire Week?] = [COLOR=#0070FF]TRUE[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR], [Count of Days] * [COLOR=#EE7F18]8 [/COLOR]+ [COLOR=#EE7F18]0[/COLOR],
[Not Employed?] = [COLOR=#0070FF]TRUE[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR], [COLOR=#EE7F18]0[/COLOR],
[COLOR=#EE7F18]40
[/COLOR][COLOR=#969696])[/COLOR][COLOR=#969696])[/COLOR]

As mentioned, there are potentially many nuances with this type of calculation, e.g. holidays, termination dates on weekends etc... My example is very basic and doesn't account for these.

There are also many other ways to approach this. My advice is to rethink your problem and consider calculating it on a daily basis (if possible) instead of your requirements which are weekly. This is how Power Pivot models should be designed, i.e. account for all possible dimensions in the model. This almost always means calculating at the lowest granularity.

Thank you so much for this. I will definitely dig into it later. I am definitely going to read the Italians' blog and book as well now that I am through the basics. I am very very interested in becoming as good at this as I am in Excel given my business thrives on the Advanced Excel stuff. If I can tailor that up into hosted BI solutions, all the better. I like staying inside of Office given I can sell services and applications for software that all businesses already own so their investment is very straightforward and doesn't require a ton of overhead. I see the PowerBI stack as an amazing evolution. I am late to the party, but ready. I have owned Rob's book since it came out, but have been doing just fine without PowerBI so I sidelined it. Now that most people are on 2013/2016, it's a much easier transition to put them on the new stack versus just basic Excel, where applicable. Always easier to learn with real data challenges!

Thank you so much for thinking through this problem with me and helping me to approach it new ways, SimonNU. I am very curious to learn more.

As a note on that: In all my years operationally, I have never really thought of utilization as anything other than weekly at the lowest level. I don't think anyone has ever asked me for something at a daily level. I understand what you're saying about working at the lowest level of granularity, which is always my preference, but I am not clear how that would be beneficial here. It actually seems like it would actually be more complicated, but I am excited to see what you did and how I can apply it. Thank you so much!

 

[analyst44]

Sorry for the late reply, I don't visit these forums often.

I've developed a simple solution for you located here.

It requires Power Pivot for Excel 2013 or greater. If you want to refresh the data then it requires Power Query.

I tried to develop it in a simple manner. It's not an efficient solution but I hope it will demonstrate how you can compartmentalize your problem into manageable parts.

Overview:

I created three measures based on your IF statement:

1. Termination or Hire Week?
2. Not Employed?
3. Count of Days

These then mashed them into this formula:

[COLOR=#0070FF]SUMX[/COLOR][COLOR=#969696] ([/COLOR]
[COLOR=#0070FF]SUMMARIZE[/COLOR][COLOR=#969696] ([/COLOR] fCradle, dStaffList[Employee Code], dDate[YYYYWW] [COLOR=#969696])[/COLOR],
[COLOR=#0070FF]SWITCH[/COLOR][COLOR=#969696] ( [/COLOR][COLOR=#0070FF]TRUE[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR],
[Termination or Hire Week?] = [COLOR=#0070FF]TRUE[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR], [Count of Days] * [COLOR=#EE7F18]8 [/COLOR]+ [COLOR=#EE7F18]0[/COLOR],
[Not Employed?] = [COLOR=#0070FF]TRUE[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR], [COLOR=#EE7F18]0[/COLOR],
[COLOR=#EE7F18]40
[/COLOR][COLOR=#969696])[/COLOR][COLOR=#969696])[/COLOR]

As mentioned, there are potentially many nuances with this type of calculation, e.g. holidays, termination dates on weekends etc... My example is very basic and doesn't account for these.

There are also many other ways to approach this. My advice is to rethink your problem and consider calculating it on a daily basis (if possible) instead of your requirements which are weekly. This is how Power Pivot models should be designed, i.e. account for all possible dimensions in the model. This almost always means calculating at the lowest granularity.

Just want to bump this to thank you again for such a detailed example/workbook/explanation. I've been able to work through everything you gave me and get it all to work with my own version of the model (with some nuances/changes).

The only thing I can't seem to understand/figure out is the following:

=SUMX (
    SUMMARIZE ( fCradle, dStaffList[FULL NAME], dDate[YYYY-WW] ),
    SWITCH (
        TRUE (),
        [Termination or Hire Week?] = TRUE (), [Count of Days] * 8 + 0,
        [Not Employed?] = TRUE (), 0,
        40
    )
)

The only thing that is different in my formula than in your formula is you used a Staff ID (which I don't have) and I'm using Staff Full Name. I am getting the following error:

Calculation error in measure 'fCradle'[Available Hours]: DAX comparison operations do not support comparing values of type Integer with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.

If I am understanding that correctly, that would seem to come from the Switch statement, but I am not sure why when I run your calculation it works but with mine I get that error. They both have Booleans and Integers in them.

Any thoughts?

Thank you again. This has been an incredible learning experience for me.