Forum Discussion

balumaran's avatar
balumaran
Frequent Visitor
9 years ago
Solved

Cumulative Sum With Slicer

Hi,  I am fairly new to Power BI and trying to find my ways in .    I have a requirement to display YTD value ( running sum) on a sales measure with a Month Slicer.  I was able to get the running ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Of course!

    mattbrice is right.

    In order to get YTD using ALL you need to use this:

    YTDSales = CALCULATE([Sales],FILTER(ALL('Business Date'),'Business Date'[Date]<MAX('Business Date'[Date])&&'Business Date'[Year]=YEAR(MAX('Business Date'[Date]))))

    or just use this staightforward formula:

    YTDSales = TOTALYTD([Sales],'Business Date'[Date])

     

    And make sure you use Date column from your 'Business Date' table in slicer

    Michael

balumaran's avatar
balumaran
Frequent Visitor
9 years ago

Thanks Michael. Let me give it a try and get back to you. 

- Mani

mattbrice's avatar
mattbrice
Solution Sage
9 years ago

I hope it is ok to jump into this thread....

 

A couple of observations.   

 

"YTDSales = CALCULATE([Sales],FILTER(ALL('Business Date'),'Business Date'[Date]<MAX('Business Date'[Date])))"

 and

"YTDSales = TOTALYTD([Sales],'Business Date'[Date]) "

 

the two formulas will only return the same value if "Business Date" table only has dates for the current year or if only have Sales data for current year  - otherwise as is the first formula will return a Life-to-Date value.  Need to add : " && YEAR('Business Date'[Date]) = YEAR ( MAX ('Business Date'[Date] ) )" to the FILTER expression to get YTD.

 

And to the OP, the reason your code didn't work using ALL is because it appears you tried to apply it to a single column ALL('Business Date'[Date]) and not the whole 'Business Date' table.  This left your slicer filter on 'Business Date[Month]' intact which is why you got the month-to-date number.   Filters are column specific.  Anonymous code works because his ALL('Business Date') removed the filter on all columns including removing the slicer filter on the month. 

 

Hope this helps...

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Of course!

    mattbrice is right.

    In order to get YTD using ALL you need to use this:

    YTDSales = CALCULATE([Sales],FILTER(ALL('Business Date'),'Business Date'[Date]<MAX('Business Date'[Date])&&'Business Date'[Year]=YEAR(MAX('Business Date'[Date]))))

    or just use this staightforward formula:

    YTDSales = TOTALYTD([Sales],'Business Date'[Date])

     

    And make sure you use Date column from your 'Business Date' table in slicer

    Michael

  • balumaran's avatar
    balumaran
    Frequent Visitor
    9 years ago

    Thanks Matt & Michael