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.