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

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