Injury Frequency Rate Moving Annual Calculation

[GregM]

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]

Hi @GregM,

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

Best Regards,
Qiuyun Yu

[GregM]

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
Month	LTI				Company	Month	Hours
Jul-15	0				ABC	Jul-15	70,415
Aug-15	1				ABC	Aug-15	73,051
Sep-15	0				ABC	Sep-15	71,654
Oct-15	0				ABC	Oct-15	73,992
Nov-15	0				ABC	Nov-15	71,653
Dec-15	0				ABC	Dec-15	73,992
Jan-16	0				ABC	Jan-16	75,327
Feb-16	0				ABC	Feb-16	70,556
Mar-16	0				ABC	Mar-16	75,687
Apr-16	0				ABC	Apr-16	73,842
May-16	2				ABC	May-16	76,440
Jun-16	1				ABC	Jun-16	74,206
Jul-16	0				ABC	Jul-16	75,876
Aug-16	1				ABC	Aug-16	75,122
Sep-16	0				ABC	Sep-16	71,218
Oct-16	1				ABC	Oct-16	73,992
Nov-16	0				ABC	Nov-16	71,169
Dec-16	0				ABC	Dec-16	73,616
Jan-17	2				ABC	Jan-17	73,427
Feb-17	0				ABC	Feb-17	65,790
Mar-17	0				ABC	Mar-17	71,921
Apr-17	0				ABC	Apr-17	69,101
May-17	0				ABC	May-17	76,440
Jun-17	0				ABC	Jun-17	70,980

[v-qiuyu-msft · Accepted solution]

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]

Excellent, works well. Thank you very much. GregM