Hi team, long time lurker first time poster. Have a question that has me beat, hoping you can help.
My data doesn't involve figures, but rather rows of safety incident details. Most of my measures involve using countrows calculations to track incident rates for the month based on certain parameters.
One of these is injury details, where I use a measure count of serious injuries for each department each month, divided by the number of hours worked in a different table, multiplied by 1,000,000 to create a frequency rate which I graph over a 12 month period. I have a separate dedicated date table that I use for the months.
However, as well as the monthly rate, I need a 12 month rolling rate that takes each month point on the graph and does a count of the previous 12 months injuries, which I can then divide by the sum of the previous 12 months hours.
So for example the point on the graph for April 2019 would be using the totals of May18 to Apr19, then March 2019 would use the totals of Apr18 to Mar19, and so on backwards. It's the last 12 month thing that's got me stumped.
