Cumulative with a slicer

[Aree]

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.

[Aree · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Try this measure

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

Hope this helps.

[Anonymous]

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

[Aree]

Hi,

This is a sample file a similar structure and issue

Pbix Cumulative issue

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[Aree]

@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")))

[Ashish_Mathur]

You are welcome.