Forum Discussion
Helper IIRolling 12 Month Average for Injuries
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.
Helper IISteve, 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?
Helper IISteve 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.
- stevedep
Memorable Member
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.
- 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. - stevedep
Hi, was busy, but will look today for sure.
- stevedep
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.
- sfalk781
Steve - fantastic! It works and I would have never figured it out without your help.
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
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.
