Measure to split date ranges into individual dates? Require column chart with total hours per month.

[aldousjg]

Hi all,

I have data on absences in the following format:

Absence Start Date	Absence End Date	Total Days	Total Hours
01/05/2021	06/05/2021	6	24
16/05/2021	03/06/2021	18	90
18/06/2021	24/06/2021	7	28
14/04/2021	12/07/2021	90	540
19/06/2021	01/07/2021	13	91

I need to deliver a column chart that has the sum of absence hours in each month. Because absences can go over multiple months, I need to create a measure to allocate the absence hours to the correct days within the range. For example:

Absence 14/04/2021 to 12/07/2021 is 90 days and 540 hours which is 6 hours per day - 17 days of the absence are in April so 17 * 6 = 102 hours in April, 31 days of the absence are in May so 31 * 6 = 186 hours in May, 30 days of the absence are in June so 30 * 6 = 180 hours in June, and 12 days of the absence are in July so 12 * 6 = 72 hours of the absence in July.

I have been trying to use GENERATE and SUMMARIZE in a measure along with the absence table and a calendar table, but cannot get the desire result.

Is anyone able to help?

Thank you,

 

[The_Macrotect]

Hello,

I'm having some trouble understanding what you're looking for. Can you please post what the results should look like from your example?