cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 applyGraph after fileters apply

 

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Cumulative (running) total working with filters

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Re: Cumulative (running) total working with filters

Hi @Ashish_Mathur,

 

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

 

Calculation stops at last data point marked as injuy.

 

Thanks!

Highlighted
Super User IV
Super User IV

Re: Cumulative (running) total working with filters

Hi,

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Anonymous
Not applicable

Re: Cumulative (running) total working with filters

Highlighted
Super User IV
Super User IV

Re: Cumulative (running) total working with filters

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

 

Untitled.png

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Anonymous
Not applicable

Re: Cumulative (running) total working with filters

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!

Highlighted
Super User IV
Super User IV

Re: Cumulative (running) total working with filters

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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors