Forum Discussion

PowerBI101's avatar
PowerBI101
Frequent Visitor
7 years ago
Solved

How to make a rolling 12-month calculation?

I'm trying to do a 12-month trailing (rolling) calculation of LTIF (Lost Time Incident Frequency) for my report. The calculation formula itself isn't the issue, but I'm having trouble calculating the rolling value of 12 months.


For reference, LTIF is calculated as (Number of injuries)*1000000/(Worked hours)

 

The calculation consists of two relevant tables. First, we have IncidentReports, which contains the incidents. Each incident has a date of occurance, IncidentReports[Date]. I have also made a measure called [LTI] (with COUNTROWS), which calculates the number of incidents. It works correctly and can be used in the LTIF formula.

 

The second table is TimeRecords. Each day has multiple rows for worked hours. The relevant columns are TimeRecords[Date] (for which date this row is) and TimeRecords[Hours] (how many hours this record contains).

 

I also have a Time table. The relevant column is Time[Date], which contains each date. I have relations from IncidentReports[Date] to Time[Date] and TimeRecords[Date] to Time[Date]. I'm aware that this table is needed for the Power BI time intelligence to function, but I'm not sure how.

 

I have searched the Internet and this forum for help regarding this issue, but I haven't been able to correctly calculate the LTIF for each month. Due to the nature of my data (multiple rows for each day of worked hours and one row for each incident), I can't provide sample data. However, I have attached some sample data that I collected from my visual below. I'm unable to calculate the LTIF measure (rolling 12 months) in Power BI, I calculated them by hand. They coul be used in a table visual or a chart visual for example.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi PowerBI101 ,

    You can use date function to convert  year month fields to date value and calculate out previous 12 month date, then use convert date value as filter condition to summarize LTIF filed values.

    Measure =
VAR currYear =
    MAX ( Table[Year] )
VAR currMonth =
    MAX ( Table[Month] )
RETURN
    CALCULATE (
        SUM ( Table[LTIF] ),
        FILTER (
            ALLSELECTED ( Table ),
            DATE ( [Year], [Month], 1 )
                >= DATE ( [Year] - 1, [Month], 1 )
                && DATE ( [Year], [Month], 1 ) <= DATE ( currYear, currMonth, 1 )
        )
    )

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi PowerBI101 ,

    You can use date function to convert  year month fields to date value and calculate out previous 12 month date, then use convert date value as filter condition to summarize LTIF filed values.

    Measure =
VAR currYear =
    MAX ( Table[Year] )
VAR currMonth =
    MAX ( Table[Month] )
RETURN
    CALCULATE (
        SUM ( Table[LTIF] ),
        FILTER (
            ALLSELECTED ( Table ),
            DATE ( [Year], [Month], 1 )
                >= DATE ( [Year] - 1, [Month], 1 )
                && DATE ( [Year], [Month], 1 ) <= DATE ( currYear, currMonth, 1 )
        )
    )

    Regards,

    Xiaoxin Sheng

  • chavanr's avatar
    chavanr
    Icon for Resolver I rankResolver I
    4 years ago

    Hi Anonymous 

    I have a similar measure to calculate 12 months rolling LTIFR, frequency rates.

    I have a calculated measure for LTIFR.

     

    I have done the below but it is giving me incorrect values.

    Rolling 12 Months FR =
    VAR sd =
    LASTDATE(DATEADD(Dates[Date],-1,YEAR))
    RETURN
    CALCULATE(
    SUMX(
    SUMMARIZE(
    Dates,
    Dates[Year],
    Dates[Month],
    "LTIG2", 'Frequency Rates'[LTI FR]),'Frequency Rates'[LTI FR]),
     
     
     
    FILTER(
    ALL( Dates[Date]),
    Dates[Date] >sd && Dates[Date] <= MAX(Dates[Date])))