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

Thanks for your help so far.  I've gone back in and added a date column to my date dimension table and linked it to the relevant data sources.  Now there's a specific date instead of using month year.  I modified the TRIR calculation to read as follows:

 

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))
 
First question is; does it look right for a rolling 12 month calculation?  Next, when I added this to a visual, it errors with "Calculation Error in TRIR2 the alternate result to return on divide by zero cases must be a constant numeric value".  Does this mean that a zero is needed somewhere in the formula and if so, where?
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.