Spread duration (datediff) over months

[DataAnalystKD]

Hello, I'm working on data from an OSH software, which are stored in a SQL database.

Below there's an Excel reproduction of some rows and columns of my fact table. For each injury, "From" indicates the first day of absence from work, "To" indicates the last day and "Total Days" naively indicates the total amount of days lost. I have no possibility of changing this schema since it comes from a software. This fact table is related to a Calendar table, via "Event Date".

To calculate and represent in Power BI a  statistical index representing injury severity, I need for each mont the number of injuries  and the number of days lost. Well, it's quite simple if the total number of days lost is bestowed on the month during which the injury happened. Doing so, with a simple DAX measure as  

CALCULATE(SUM(FactTable[AnswerIntValue]),  FactTable[QuestionId] = 13)

 I was able to get the desired visuals based on a table similar to the one on the left below.

However, my customer wants the days lost to be distributed over the corresponding months, so I need a measure which can give the result shown in the table on the right.

 I'm stuck on this. Any idea please?

[v-rzhou-msft]

Hi @DataAnalystKD ,

My Calendar table:

Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Month/Year", FORMAT ( [Date], "MMMM YYYY" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Data model:

Measure:

Days lost = 
VAR _STEP1 =
    ADDCOLUMNS (
        FILTER ( ALL ( FactTable ), FactTable[Answer DateTime Value] <> BLANK () ),
        "YearMonth",
            YEAR ( FactTable[Answer DateTime Value] ) * 100
                + MONTH ( FactTable[Answer DateTime Value] )
    )
VAR _STEP2 =
    ADDCOLUMNS (
        _STEP1,
        "Days lost",
            VAR _COUNT =
                COUNTAX (
                    FILTER (
                        SUMMARIZE ( _STEP1, [Injury Id], [YearMonth] ),
                        [Injury Id] = EARLIER ( [Injury Id] )
                    ),
                    [YearMonth]
                )
            VAR _START =
                EOMONTH ( [Answer DateTime Value], -1 ) + 1
            VAR _END =
                EOMONTH ( [Answer DateTime Value], 0 )
            VAR _Total =
                CALCULATE (
                    SUM ( FactTable[AnswerIntValue] ),
                    FILTER (
                        FactTable,
                        FactTable[Injury Id] = EARLIER ( [Injury Id] )
                            && FactTable[QuestionId] = 13
                    )
                )
            VAR _Result =
                IF (
                    _COUNT = 1,
                    _Total,
                    IF (
                        [QuestionId] = 11,
                        DATEDIFF ( [Answer DateTime Value], _END, DAY ) + 1,
                        DATEDIFF ( _START, [Answer DateTime Value], DAY ) + 1
                    )
                )
            RETURN
                _Result
    )
VAR _Final =
    SUMMARIZE ( _STEP2, [Injury Id], [YearMonth], [Days lost] )
RETURN
    SUMX (
        FILTER ( _Final, [YearMonth] = MAX ( 'Calendar'[YearMonth] ) ),
        [Days lost]
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataAnalystKD]

Hello @v-rzhou-msft , 

thank you for your answer. Your solution works with my current data (see image below, the newly added "Days Lost" column next to my old version, "Giorni assenza nel mese" - language is italian).

 
However I think further improvements will be needed.  

1) If the absence due to an injury spans over more than two months, I need the days lost to be computed for each involved month. I tried this by manually changing in Power Query the end date of the first injury, from 5 february to 5 march, and the result is the following:

 The 28 expected days for february are missing.

2) If more than one injury happen during the same month, I need the total days lost to be computed accordingly. I tested this by manually changing the dates of the april injury, replacing the month from april to january, thus having 16 expected days lost in january. This is what happens:

 Apparently, only the days of the actual january injury are computed. 

However, if i change only the start date of the second injury, from 4 april to 4 january, this is what happens:

 Now in january I have 41 days, that is 13 (first injury from 19 to 31 january) plus 28 (second injury from 4 to 31 january) which is correct. As said above, the days lost in the intermediate months are not computed.

3) For the statistical indexes, I need a rolling sum over the last 12 months (even if for now I have only 4 months available). It worked with my previous measure, values are shown in the "Giorni assenza ultimi 12 mesi" column, containing a measure which calls back and processes the "Giorni assenza nel mese" measure. However, if i replace the latter in the rolling sum measure with the new measure "Days Lost", i get just a copy of Days Lost, see below.

How can I address this issues? Any further help would be greatly appreciated.