Cumulative (running) total working with filters

[Anonymous]

Hi All,

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]) ),
    BLANK (),
    CALCULATE (
        SUM( OHS_Register[Counter] ),
        DATESBETWEEN (
           Dates[Date],
            FIRSTDATE ( DATEADD ( Dates[Date], -12, MONTH ) ),
            ENDOFMONTH ( DATEADD ( Dates[Date], 0, MONTH ) )
        )
    )
)

How do I correct this?Graph after fileters apply

[Ashish_Mathur]

Hi,

That is happening because of your ISBLANK() statement.  Remove that.

[Anonymous]

Hi @Ashish_Mathur,

I try that one but it didn't work either.

Calculation stops at last data point marked as injuy.

Thanks!

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[Anonymous]

Hi @Ashish_Mathur,

Please use the below link,

https://drive.google.com/open?id=11Ia9KuyS_-mPlONAqiDNaqTLhMj4Kscu

Thanks!

[Ashish_Mathur]

Hi @Anonymous,

Try these formulas

TRI mod = CALCULATE(SUM( OHS_Register[Counter]),DATESBETWEEN(Dates[Date],MINX(ALLSELECTED(Dates[Date]),Dates[Date]),MAX(Dates[Date])))

 and

TRIFR mod = ([TRI mod]/[Total_Hours])*1000000

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur,

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.

Thanks!

[Ashish_Mathur]

Hi,

The data range will be dependent on the selection made in the slicer - i believe that is what you want.  Please recheck.