Forum Discussion

DataAnalystKD's avatar
DataAnalystKD
Frequent Visitor
3 years ago

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" indicat...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    DataAnalystKD
    Frequent Visitor
    3 years ago

    Hello Anonymous , 

     

    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.