Forum Discussion

sfalk781's avatar
sfalk781
Icon for Helper II rankHelper II
6 years ago
Solved

Rolling 12 Month Average for Injuries

Here's one that I just can't figure out and am wondering if you could help me.  I'm coming from excel and just learning.  I have an injury table that has all of the injuries that occured for a given ...
  • stevedep's avatar
    stevedep
    6 years ago

    Hi,

    sry for delay, busy times.

    Here is the code:

    TRIR-Rolling = CALCULATE (
DIVIDE (   
        CALCULATE(COUNT ( INJ[Incident Type] );  'INJ'[Incident Type] IN { "Recordable Injury" })       
          * 200000;
    SUM ( 'Hours'[Hours] ))
 ; DATESINPERIOD (
        'Date Table'[Date];
        LASTDATE ( 'Date Table'[date] );
        -12;
        MONTH
    ) )

    As seen here:

    File is here (ps. added some measures for validation). 

     

    Hope this works for you, is so pls mark as solution. 

     

    Kind regards, Steve. 

     

sfalk781's avatar
sfalk781
Icon for Helper II rankHelper II
6 years ago

Steve, 

I'm still struggling with this. I think what you're saying is that the Datesinperiod is outside the division causing the problem.  Does this mean that it should be somewhere else near the front?  

 

When you say "should be wrapped with calculate for context transition?" are you asking me or others?  Is there any suggestion on how to reformulate this so that it works? I'm dead stopped with this.  The existing formula is below.

 

TRIR2 = Divide(calculate(COUNT(INJ[Incident Type]),'INJ'[Incident Type] IN { "Recordable Injury" } )*200000,sum('Emp Hours Raw'[HOURS]),DATESINPERIOD('DimDate (2)'[Calendar Date],lastdate('DimDate (2)'[Calendar Date]),-12,MONTH))
 
thanks again for helping.
sfalk781's avatar
sfalk781
Icon for Helper II rankHelper II
6 years ago

Does anyone have any other ideas on how I could possibly change this formula so that it works based on the information provided here?  Thanks again for your help.