Summarising Working Days

DAX DAX Calculations
1

I’m trying to how many working days each employee has done. I’ve got a slicer with month/year and it’s working fine currently.

However, the total value for all employees is off that I’m trying to show in a card. It may be because of the granulatity my DAX is at currently, I feel like I need to use summarize or something but that’s beyond my level at the moment so can’t seem to crack it.

Here is my measure currently that is working as expected at employee level but not overall:

Working Days = 
    VAR vEmpStartDate = SELECTEDVALUE( 'Employees'[Start Date] )
    VAR vEmpEndDate = SELECTEDVALUE( 'Employees'[Leave Date] )

    VAR vMinDate = MIN( Dates[Date] )
    VAR vMaxDate = MAX( Dates[Date] )

    VAR vStartDate = IF( vMinDate > vEmpStartDate, vMinDate, vEmpStartDate )
    VAR vEndDate = IF( vMaxDate < vEmpEndDate, vMaxDate, vEmpEndDate )

    RETURN
    CALCULATE(
        SUM( Dates[WorkingDay] ),
        DATESBETWEEN( 'Dates'[Date], vStartDate, vEndDate )
    )

For example, selecting November 2021 shows all as 22 apart from Emp13 who just recently started, so they have 7 which is correct:

image
image697×470 20 KB

However the total/if I put it in a card shows 43. I’d expect this to show 183 as that’s the total days for the employees. I’m not sure how best to do this, if it needs to be a seperate measure that’s not a problem!

Working Days Issue.pbix (75.0 KB)

2

Hello @jamie.bryan,

Thank You for posting your query onto the Forum.

Just write the below provided measure -

Working Days - Totals = 
SUMX(
    SUMMARIZE(
        Employees , 
        Employees[Employee] , 
        Employees[Start Date] , 
        Employees[Leave Date] , 
        "@Totals" , 
        [Working Days] ) , 
    [@Totals]
)

Final Results
Final Results1220×482 27.4 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Working Days Issue - Harsh.pbix (75.7 KB)

2 Likes
3

That seems to be working perfectly, thank you so much!

1 Like