topic Re: Rolling 12 Month Average for Injuries in DAX Commands and Tips

Rolling 12 Month Average for Injuries

sfalk781 — Wed, 24 Jun 2020 14:39:18 GMT

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?

Re: Rolling 12 Month Average for Injuries

lbendlin — Wed, 24 Jun 2020 20:36:33 GMT

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.

Re: Rolling 12 Month Average for Injuries

stevedep — Wed, 24 Jun 2020 21:13:32 GMT

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

Re: Rolling 12 Month Average for Injuries

sfalk781 — Thu, 25 Jun 2020 13:46:46 GMT

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?

Re: Rolling 12 Month Average for Injuries

stevedep — Thu, 25 Jun 2020 16:02:53 GMT

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

Re: Rolling 12 Month Average for Injuries

stevedep — Thu, 25 Jun 2020 20:31:31 GMT

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

Re: Rolling 12 Month Average for Injuries

sfalk781 — Thu, 25 Jun 2020 23:17:10 GMT

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?

Re: Rolling 12 Month Average for Injuries

stevedep — Fri, 26 Jun 2020 04:24:48 GMT

Is your date filter a parameter in your divide statement?

Re: Rolling 12 Month Average for Injuries

sfalk781 — Fri, 26 Jun 2020 12:15:35 GMT

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:

Is there anything else I can provide to help get closer to solving this?

Re: Rolling 12 Month Average for Injuries

stevedep — Fri, 26 Jun 2020 12:51:34 GMT

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.

Re: Rolling 12 Month Average for Injuries

sfalk781 — Fri, 26 Jun 2020 16:15:13 GMT

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.

thanks again for helping.

Re: Rolling 12 Month Average for Injuries

sfalk781 — Sat, 27 Jun 2020 13:32:45 GMT

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.

Re: Rolling 12 Month Average for Injuries

stevedep — Sat, 27 Jun 2020 15:44:08 GMT

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)

Re: Rolling 12 Month Average for Injuries

sfalk781 — Sat, 27 Jun 2020 18:54:03 GMT

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?

Re: Rolling 12 Month Average for Injuries

stevedep — Sat, 27 Jun 2020 19:07:55 GMT

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.

Re: Rolling 12 Month Average for Injuries

sfalk781 — Sat, 27 Jun 2020 19:34:29 GMT

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?

Re: Rolling 12 Month Average for Injuries

stevedep — Sat, 27 Jun 2020 19:46:46 GMT

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-p/963216

Sharing file is even better. Onedrive

Re: Rolling 12 Month Average for Injuries

sfalk781 — Sun, 28 Jun 2020 16:34:40 GMT

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

Re: Rolling 12 Month Average for Injuries

stevedep — Sun, 28 Jun 2020 19:17:14 GMT

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

Re: Rolling 12 Month Average for Injuries

sfalk781 — Tue, 30 Jun 2020 21:26:07 GMT

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.