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

stevedep and others, I tried to use the formula provided but it was giving me constant errors with the semicolon.  I'm not sure if they're supposed to be there or not.   Separately, my dimdate table is attached.  The INJ table has a "month-year" column as well and that's how the two are linked together.  Is doing this even possible this way without creating a complete calendar table covering years of data?

 

After a day and a half of searching, I was able to come up with this formula:

TRIR = Divide(calculate(COUNT(INJ[Incident Type]),'INJ'[Incident Type] IN { "Recordable Injury" } )*200000,sum('Emp Hours Raw'[HOURS]),DATESINPERIOD(DimDate[MonthYear],lastdate(DimDate[MonthYear]),-12,MONTH))

 

but it still doesn't work.  It's saying that "A column specified in the call to function "Last Date" is not of type DATE."  Seeing this, I went in to the dimtable and changed Month-Year from text to date, but then its erroring again with  "the alternate result to return to divide by zero cases must be a constant numeric value".

 

Any other suggestions based on this update? 

 

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

    Month year is not a date is just a number which reflects the month year.