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