# Summarising Working Days

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:

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)

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]
)
``````

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.

Thanks and Warm Regards,
Harsh

Working Days Issue - Harsh.pbix (75.7 KB)

2 Likes

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

1 Like