Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Spread duration (datediff) over months

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?

Community Support
Community Support

Hi @DataAnalystKD ,


My Calendar table:

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

Data model:



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


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.



Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors