Calculating Data by Date Range in Power BI

[zondan]

Hi,

I'm struggling a bit with one of the measures. Basically want to calculate Total Days of Holidays per employee and show this by Day (e.g. 13/07/2016, 6 people off). Holiday table is in the following format (so you can have multiple date ranges for the same employee):

Employee, HolidayStartDate, HolidayEndDate 

And then I've got a standard Date Dimension Table, so I can display number of people on holiday per day. I tried with DATESBETWEEN, DATESINPERIOD, FIRSTDATE etc. but the problem seems to be that I've got multiple holiday date ranges per employee. It must be an easy solution as this scenario happens in many data models (check if calendar date falls in one of the data blocks in other table) but I can't find anything on forum. Any help will be appeciated.

The wrong DAX I was using in my Measure was: CALCULATE(SUM[DurationDays], DATESBETWEEN(Calendar[Date], Holidays[StartDate],Holidays[EndDate])

Thanks in advance.

Daniel

[jahida · Accepted solution]

I was never much good with time intelligence, this is pretty solvable without that though. Maybe try something like:

Measure = COUNTROWS(FILTER(

                          Holidays, Holidays[StartDate] <= LASTDATE(Calendar[Date]) && Holidays[EndDate] >= FIRSTDATE(Calendar[Date])

                   ))

This would count the number of Employees on holiday at any point during the date range selected. Note that it will not give good results if you try to pick ranges with gaps in them (ie. if you tried to pick Jan 2015 and Jan 2016, it would give all of 2015 + Jan 16).

A very similar measure could be written with the Calculate function, I just find this easier to read.

EDIT: If you're trying to get the number of days taken off, swap COUNTROWS out for SUMX.

[zondan]

Thanks Jahida,

I'll give it ago tomorrow and let you know.

Thanks,

Dan

[zondan]

🙂 Thanks, it worked. Appreciate your time. I was pretty sure I tried something similar, proably getting older. I now need to add something to keep filter context when I additionaly filter by Employee.

Dan