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.