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?...
  • 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

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

      • GregM's avatar
        GregM
        New Member
        9 years ago

        Excellent, works well. Thank you very much. GregM