I have create a chart to capture 12 months running total of injuries. Chart works fine without filters. However, if you filter data by region/branch then caclulation stops at last recorded injury date for that region/branch as shown in attached image.
Since this is running figure I expect it to continue the calcluation till last date irrespective of injury is recorded or not. (So 12 months total will decrease for those regions/branches)
Below is the formula I use.
TRI = IF (
ISBLANK ( SUM ( OHS_Register[Counter]) ),
SUM( OHS_Register[Counter] ),
FIRSTDATE ( DATEADD ( Dates[Date], -12, MONTH ) ),
ENDOFMONTH ( DATEADD ( Dates[Date], 0, MONTH ) )
How do I correct this?
That is happening because of your ISBLANK() statement. Remove that.
Try these formulas
TRI mod = CALCULATE(SUM( OHS_Register[Counter]),DATESBETWEEN(Dates[Date],MINX(ALLSELECTED(Dates[Date]),Dates[Date]),MAX(Dates[Date])))
TRIFR mod = ([TRI mod]/[Total_Hours])*1000000
Hope this helps.
This formula gives cumulative total from the begining. I want is for moving total for 12 months foe each month and in my table it didn't work for eastern and western regions.
The data range will be dependent on the selection made in the slicer - i believe that is what you want. Please recheck.
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!
Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications