cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Cumulative (running) total working with filters

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

7 REPLIES 7
Super User III

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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

Calculation stops at last data point marked as injuy.

Thanks!

Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable
Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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!

Super User III

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks