Forum Discussion

Aree's avatar
Aree
Resolver I
8 years ago
Solved

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('Calenda...
Need Help
  • Aree's avatar
    Aree
    8 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Aree
    Resolver I
    8 years ago

    Hi,

     

    This is a sample file a similar structure and issue

    Pbix Cumulative issue

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      You may refer to my solution here.

       

      Hope this helps.

       

      • Aree's avatar
        Aree
        Resolver I
        8 years ago

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