Rolling 12 Month Average for Injuries

[sfalk781]

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 period including date and other attributes.  I also have a separate hours table that has all hours worked by a location.  The incidents have individual dates but the hours don't because the source where I calculate them from only sends them once per month.  Note that Incidents and Hours are linked with a location.  For this reason, I also have a date dimension table that normalizes both the injury and hours table down to "month-year".  All of this is linked together within the model tab so that the relationships have been established.

I wrote a formula in DAX that calculates the incident rate for a given type of injury called "Recordable Injury" which is (injuries x 200,000)/Total Hours Worked.  Here's what it looks like and I tested that it is working. 

TRIR = DIVIDE(CALCULATE(COUNT(INJ[Incident Type]),'INJ'[Incident Type] IN { "Recordable Injury" } )*200000,sum('Emp Hours Raw'[HOURS]))

What can I add to this formula to get it to look back over the previous 12 months so that it's a rolling calculation?  Also, since the incidents have individual dates, but the hours don't (both of these are connected via month - years) how does that influence this calculation?

[stevedep]

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Sharing file is even better. Onedrive

[stevedep]

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

[stevedep]

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[sfalk781]

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]

Is your date filter a parameter in your divide statement?

[sfalk781]

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]

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]

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]

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)

[stevedep]

TRIR2 = calculate(
Divide(
COUNT(INJ[Incident Type]),'INJ'[Incident Type] IN { "Recordable Injury" } )*200000, sum('Emp Hours Raw'[HOURS] ),
Filter (all(dimdate (2)),
DATESINPERIOD('DimDate (2)'[Calendar Date],lastdate('DimDate (2)'[Calendar Date]),-12,MONTH))

If this does not work, I suggest to share a sample file.

[sfalk781]

Steve, still the same problem.  Should I create a new PBIX file with anonymized data?  If so, how do I upload it - I can't seem to find how to do it?

[sfalk781]

Steve and others, I've spent hours recreating this over the last day and a half with ficticuous data.  I created an open one drive link to see the files and here's how it's laid out.

1 - there's a file in the folder called Excel Example - Look Here First - Please look at that first to see what it looks like created in excel

2 - I created a new PBIX file with the tables and measures set up exactly like how I started (the problem is the same)

3 - The source files for injuries and hours are there

4 - I added some commentary

Please - if anyone could assist with this, I'd really appreciate it.

https://1drv.ms/u/s!Ag-g0BcIy_d1grR3TD3dGKzTZtAnJg?e=vQc4pt 

[stevedep]

Thanks, I will help you tomorrow, should be easy to get in order.

[sfalk781]

@stevedep Just checking back in with this to see if anyone had a chance to try and figure this out?  I worked through the weekend on it and am no further than I was before.  It's showing the same exact error in the sample I provided as well as the real dataset.  Any help is greatly appreciated.

[stevedep]

Hi, was busy, but will look today for sure.

[stevedep · Accepted solution]

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. 

[mguidry5]

How did you tie the injury table and the hours table together using the "Site" column? This would be a many-many relationship, and it won't let me establish this with both the hours and injuries tables having relationships to the Date Table.

Right now, my version of this is summing ALL the hours and injuries, regardless of site.

[sfalk781]

@mguidry5 The tables (all three) are tied together based on the date column.

[kkureska]

Hello Steve

I ran across your post for calculating 12 month rolling for TRIR, but cannot open the file. Is there anyway you can send it differently with the columns and formulas. I am not an expert on excel

Thank you 

[sfalk781]

Steve - fantastic!  It works and I would have never figured it out without your help.