Forum Discussion
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 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.
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.
26 Replies
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.
calculate([trir];datesinperiod([date];-12;month))
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?
Month year is not a date is just a number which reflects the month year.
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?
