Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DataAnalystKD
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".

 

01.png

 

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.

 

02.png

 I'm stuck on this. Any idea please?

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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:

vrzhoumsft_0-1682327013712.png

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.

vrzhoumsft_1-1682327047693.png

 

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).

06.png

 
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:
08.png

 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:

07.png

 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:
09.png

 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.
10.png

 

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



 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.