Forum Discussion

GregM's avatar
GregM
New Member
9 years ago
Solved

Injury Frequency Rate Moving Annual Calculation

Hi, trying to calculate Injury Frequency Rates. Able to do YTD calculation and also Fiscal year, but need to calculate the moving annual Injury Frequency Rate on a monthly basis. Anyone able to help? 

Thanks

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi GregM,

     

    In your scenario, check if the Month columns in those two tables are Date type. If not, please create a calculated column to return it as Date type.

    Date = DATEVALUE("1-"&'Hours Table'[Month])

     

    Then Create a calculated column to get moving 12 month LTIRF.

     

    Moving12LTIRF = CALCULATE(SUMX( ALLSELECTED('LTI Table'), 'LTI Table'[LTI])/SUMX( ALLSELECTED('Hours Table'), 'Hours Table'[Hours])*1000000,
        DATESINPERIOD (
            'LTI Table'[Date],
            LASTDATE ( 'LTI Table'[Date] ),
            -12,
            MONTH
        )
    )

     

     

    Best Regards,
    Qiuyun Yu

4 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    Hi GregM,

     

    Can you share some dummy data and corresponding desired results for our analysis?

     

    Best Regards,
    Qiuyun Yu

    • GregM's avatar
      GregM
      New Member
      9 years ago

      Thanks

       

      Sample data as follows.

       

      LTIFR = (LTI / Hours)*1000000

      Calculated over a 12 month period

       

       

       

      Desired results for LTIFR over rolling annual period:

       

      4.541249 (Annual period Jul-15 to Jun-16)

      4.513267 (Annual period Aug-15 to Jul-16)

      4.502745 (Annual period Sep-15 to Aug 16)

      and so on

       

      LTI Table    Hours Table 
      MonthLTI   CompanyMonthHours
      Jul-150   ABCJul-1570,415
      Aug-151   ABCAug-1573,051
      Sep-150   ABCSep-1571,654
      Oct-150   ABCOct-1573,992
      Nov-150   ABCNov-1571,653
      Dec-150   ABCDec-1573,992
      Jan-160   ABCJan-1675,327
      Feb-160   ABCFeb-1670,556
      Mar-160   ABCMar-1675,687
      Apr-160   ABCApr-1673,842
      May-162   ABCMay-1676,440
      Jun-161   ABCJun-1674,206
      Jul-160   ABCJul-1675,876
      Aug-161   ABCAug-1675,122
      Sep-160   ABCSep-1671,218
      Oct-161   ABCOct-1673,992
      Nov-160   ABCNov-1671,169
      Dec-160   ABCDec-1673,616
      Jan-172   ABCJan-1773,427
      Feb-170   ABCFeb-1765,790
      Mar-170   ABCMar-1771,921
      Apr-170   ABCApr-1769,101
      May-170   ABCMay-1776,440
      Jun-170   ABCJun-1770,980
              
              
              

       

       

       

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Icon for Community Support rankCommunity Support
        9 years ago

        Hi GregM,

         

        In your scenario, check if the Month columns in those two tables are Date type. If not, please create a calculated column to return it as Date type.

        Date = DATEVALUE("1-"&'Hours Table'[Month])

         

        Then Create a calculated column to get moving 12 month LTIRF.

         

        Moving12LTIRF = CALCULATE(SUMX( ALLSELECTED('LTI Table'), 'LTI Table'[LTI])/SUMX( ALLSELECTED('Hours Table'), 'Hours Table'[Hours])*1000000,
            DATESINPERIOD (
                'LTI Table'[Date],
                LASTDATE ( 'LTI Table'[Date] ),
                -12,
                MONTH
            )
        )

         

         

        Best Regards,
        Qiuyun Yu