DAX - Work out days in an overlapping period

Question

I am fairly new to DAX and seem to be coping ok so far but I am completely stuck on an issue. I think it relates to 'in progress' events

I have a "Leave" table which shows periods of Annual Leave, Sick, and Public Holidays. I want to calculate how much of each has been taken the previous month. To add to the complication, I only wish to calculate working days of each. I have a related "Calendar" table that has a column called "Working Day" so I've just been using COUNTROWS and filtering to Yes to only show these rows. The part I'm struggling with is sometimes the period on a row will run over more than one month as you can see with the "SICK" row. For example I'd expect the values for Sick to show -

June - 6 July - 22 August - 1

Can this be done with a measure?

Any help would be really appreciated.

The attached photo on the original question is the source table, it’s from an excel file. My desired output for SICK for example would be - Month Working Days June 6 July 22 August 1 Does this help? — JSY

msta42a msta42a · Accepted Answer · 2021-09-04T11:45:01

You may try this solution:

LeaveWorkDay = 
var __date = GENERATE(Leave, FILTER('Calendar', 'Calendar'[Date] >= [Start] && 'Calendar'[Date] <= [End] && 'Calendar'[workingdays] = 1))
return
countrows(__date)

where my test model looks like:

Used field:

Output:

DAX - Work out days in an overlapping period

1 Answers