DAX measure to calculate Working Days does not work

[GJ217]

Hi All,

The following DAX measure to caculate the number of working days for sickness absence does not work I'm getting the below error message.

Sickness Duration (Days) = CALCULATE(

COUNTROWS(Dates),

DATESBETWEEN(Dates[Date],SicknessAbsence[AbsenceStartDate],SicknessAbsence[AbsenceEndDate]),

Dates[IsWorkingDay] = TRUE(),

ALL(Dates))

"A single value for column 'AbsenceStartDate' in table 'SicknessAbsence' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I have a Dates table which is linked to my SickenessAbsence table via the AbsenceStartDate and AbsenceEndDate.

Help getting round this is much appreciated.

[GJ217]

@Greg_Deckler 

Thanks for the info.

Could you please explain what the variable Calendar2 does with the WEEKDAY function?

NetWorkDays = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

 I've gone for this option but I'm getting th value 10 appear against each month in the year which doesn't look right. My adapted DAX measure is below.

SickDurationDays =

VAR Date1 = CALENDAR(MAX(SicknessAbsence[AbsenceStartDate]),MAX(SicknessAbsence[AbsenceEndDate]))

VAR Date2 = ADDCOLUMNS(Date1,"WeekDay",WEEKDAY([Date],2))

RETURN COUNTX(FILTER(Date2,[WeekDay]<6),[Date])

Help tweaking this is much appreciated!

[Greg_Deckler]

@GJ217 Stay away from Time Intelligence functions, they are a real hassle. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also, there is a new NETWORKDAYS function in DAX now. And here is the old way: 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109