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?

[sfalk781]

Just to clarify, the goal is to get the rolling average updating monthly based on a 12 month lookback.  The formula I'm using is 

TRIR2 = Divide(calculate(COUNT(INJ[Incident Type]),'INJ'[Incident Type] IN { "Recordable Injury" } )*200000,sum('Emp Hours Raw'[HOURS]),DATESINPERIOD('DimDate'[Calendar Date],DATE(2020,1,1),-12,MONTH)) but it's returning the below error.  I've searched for blanks in the data, but can't find any.

[sfalk781]

I tried the proposed suggestion and it seems that the only difference is that we're swapping the calculate and divide at the front end and dropping a parenthesis at the end.  When doing this, it gives an error that says "A function 'sum' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

When using this proposed formula, it's recognizing the first comma as the separation beween the numerator and denominator.  I'm still stuck with this.  Any other ideas?

[sfalk781]

Does anyone have any other ideas on how I could possibly change this formula so that it works based on the information provided here?  Thanks again for your help.

[sfalk781]

@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]

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

[lbendlin]

Any serious date math requires a dates table. That table needs to be contiguous and covering.

The injury data already has dates, but (hopefully) not contiguous. For the hours table you can use a trick to assign an arbitrary day in the month to the data (say, always first day of the month, or always last day of the month).

That way you can tie both the incidents and the hours lookup to the dates table, and then you can do the YoY or rolling measures etc much easier.