cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aree Regular Visitor
Regular Visitor

Cumulative with a slicer

I working on doing a cumulative line which is basic using the standard appraoch.

Forecast Cumulative = 
 CALCULATE(
      SUM('Financial'[Forecast]), 
           FILTER(
                ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date])  )
                       )

However i would like to be able to slice the data by a given year. But the results i'm getting is wrong. 

The image shows that for Year 2019 the total should be 810k but the cumulative is 910K which is because it is pulling the value from the prior year (2018). I have tried a few different approaches but i've been hitting the proverbial wall on this one.

forecast cumulative isue.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Aree Regular Visitor
Regular Visitor

Re: Cumulative with a slicer

Found my solution after some research and testing.

 

Forecast Cumulative = 
 CALCULATE(
      SUM('Financial'[Forecast]), 
           FILTER(
                ALLSELECTED('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date])  )
                       )

ALL was causing a filter context outside of my slicer but ALLSELECTED worked like a charm.

7 REPLIES 7
Community Support Team
Community Support Team

Re: Cumulative with a slicer

HI @Aree,

 

According to your description and snapshot, your visual seems displayed incorrect records.

 

It seems like financial table not contains records before 9-2019, but calendar date and measure formula expand them with 100k to replace blank records.

 

Can you please share some sample data to help us clarify your scenario and testing?

How to Get Your Question Answered Quickly

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Super User
Super User

Re: Cumulative with a slicer

Hi,

 

Try this measure

 

=CALCULATE(SUM('Financial'[Forecast]),DATESYTD('Calendar'[Date],"31/12"))

 

Hope this helps.

Aree Regular Visitor
Regular Visitor

Re: Cumulative with a slicer

Hi,

 

This is a sample file a similar structure and issue

Pbix Cumulative issue

Super User
Super User

Re: Cumulative with a slicer

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png

Aree Regular Visitor
Regular Visitor

Re: Cumulative with a slicer

Found my solution after some research and testing.

 

Forecast Cumulative = 
 CALCULATE(
      SUM('Financial'[Forecast]), 
           FILTER(
                ALLSELECTED('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date])  )
                       )

ALL was causing a filter context outside of my slicer but ALLSELECTED worked like a charm.

Aree Regular Visitor
Regular Visitor

Re: Cumulative with a slicer

@Ashish_Mathur 

lol thanks for the effort mate. I found a solution this morning after some testing and researching. 

 

Special Mention to @Ashish_Mathur

Who offered alternate solution which is pretty sweet.

 

IF(ISBLANK([Forecasts]),BLANK(),CALCULATE([Forecasts],DATESYTD('Calendar'[Date],"30/6")))
Super User
Super User

Re: Cumulative with a slicer

You are welcome.