How to calculate number of days in each month for a period which spans to several months

[AksaS]

Hi,

I need some help to calculate correctly number of sick leave days in each month.
The problem is following, the current PowerPivot based reporting calculates number of sick leave days for a month when sick leave was given even if the sick leave continues to next month.
i.e. if 10 days sick leave is given on 31st of December all 10 days are calculated to December based on the day when sick leave was given.
This should be fixed so that 1 day is calculated to December and 9 days are calculated to January.

In the PowerPivot table where all the events are saved there are columns "SickLeaveDate" which contains the date when sick leave was given, "SickLeaveEndDate" which contains the date when sick leave ends and "SickLeaveDuration" which contains number of sick leave days. In the this example e.g. "SickLeaveDate"=12/31/2015, "SickLeaveEndDate"=01/09/2016 and "SickLeaveDuration"=10.

Can anyone help with the correct DAX formula to fix this problem?

 

[Ozeroth]

Hi AksaS,

I think the 'events in progress' pattern applies here.

I've just taken the code from page 27 of this paper and modified for your situation.
http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

I've called your leave table Leave, and I'm assuming Leave[SickLeaveDate] is related to Calendar table column Calendar[Date].

=
SUMX (
    VALUES ( Calendar[Date] ),
    SUMX (
        FILTER (
            GENERATE (
                CALCULATETABLE (
                    SUMMARIZE (
                        Leave,
                        Leave[SickLeaveDate],
                        Leave[SickLeaveEndDate],
                        "Rows", COUNTROWS ( Leave )
                    ),
                    ALL ( Calendar )
                ),
                DATESBETWEEN ( Calendar[Date], Leave[SickLeaveDate], Leave[SickLeaveEndDate] )
            ),
            Calendar[Date] = EARLIER ( Calendar[Date] )
        ),
        [Rows]
    )
)

 

[AksaS]

Hi Ozeroth,

This works perfectly in my reports, now I get reports where sick leaves are correctly reported to right months.
Big thanks for your help and quick answer