Counting Working Days for a Population of Workers with Differing Start Dates

[jl20]

Hi all,

I am trying to calculate the total possible working days. My tables are:

1) Dim Calendar (I have holidays and weekends flagged)

2) Dim Worker - includes Date of Hire

3) Fact Timecards, to which each of the tables above filters

Need a measure to calculate non-weekends, non-holidays that's dynamic regardless of how Dim Worker and Dim Calendar are being filtered. For each worker, it'll need to iterate over the calendar table and then sum them all up. For example, if I have 5 workers and there was 100 possible working days, plus 1 worker that recently started and only had 20 possible working days, the measure would return 520.

Thanks in advance for any ideas.

[amitchandak]

@jl20 , On 2nd page you have date diff with and without weekends

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[jl20]

Thanks. I was able to use a derivative of the syntax found here: https://stackoverflow.com/questions/31420864/dax-utilization-measure-formula. One follow-up question though - how would I carve out certain rows from the Worker table (first term in the SUMX), based on a dimension table attached to it? I tried FILTER, RELATED(Worker Group <> "Operations"), but that didn't seem to work. If I wrap in a calculate it messes the whole thing up. Thoughts? 

Current DAX:

Available Hours =

SUMX(

Worker,

CALCULATE(

SUM('Calendar'[Available Working Hours]),

FILTER('calendar',

'Calendar'[Time Block Date]>=FIRSTDATE(Worker[HireDate])

),

FILTER(

'calendar',

'Calendar'[Time Block Date] <=

IF(

ISBLANK(Worker[TermDate]),

TODAY(),

LASTDATE(Worker[TermDate])

)

)

)

)

[vivran22]

@jl20

Please share a sample data set with the expected output.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[vivran22 · Accepted solution]

Hello @jl20 

Minus the specific, it is difficult to suggest. You may find the following article helpful in this context:

https://www.sqlbi.com/articles/counting-working-days-in-dax/

If you need more help, please share the sample file structure.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter