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. 

     

stevedep's avatar
stevedep
Icon for Memorable Member rankMemorable Member
6 years ago

Is your date filter a parameter in your divide statement?

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

Steve,

 

I apologize, but I'm not really sure what you mean by "parameter"?  The only thing I know is the formula that I wrote which is:

 

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))

 

Is there anything else I can provide to help get closer to solving this?

  • stevedep's avatar
    stevedep
    Icon for Memorable Member rankMemorable Member
    6 years ago

    TRIR2 =

    Divide(

    calculate(

    COUNT(INJ[Incident Type])

    ,'INJ'[Incident Type] IN { "Recordable Injury" } // filter for calculate

    )*200000  // outcome of calculate * 2000

    ,

    sum('Emp Hours Raw'[HOURS] // should be wrapped with calculate for context transition?

    ) // this concludes the division

    // the below part is outside of your division statement, causing the error

    ,DATESINPERIOD('DimDate (2)'[Calendar Date],lastdate('DimDate (2)'[Calendar Date]),-12,MONTH))

     

    datesinperiod should is a filter, which is a input / parameter for a calculate statement. 

    • 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.
      • stevedep's avatar
        stevedep
        Icon for Memorable Member rankMemorable Member
        6 years ago

        I am not sure without a sample file, but perhaps this works:

        TRIR2 = 
calculate(
  Divide(
   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)