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

calculate([trir];datesinperiod([date];-12;month))

  • 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?