Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
balumaran
Frequent Visitor

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 sum working. However when I select a month in the slicer, I am expecting the running sum to calculate upto the month selected in the slicer, however running sum that I am getting is filtered for the month. 

 

sum sum.PNG

 

In the above example, when MArch 2016 is selected in the slicer , I expect the running sum /YTD sales number to show 80 instead I am getting 20. What is the best way to get the value. Appreciate the help.

-M

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

This should work:

YTDSales = TOTALYTD([Sales],CalendarTable[Date]) 

Also, make sure that you use your CalendarTable Month column in your slicer (NOT the Month from the Fact table)

Michael

Anonymous
Not applicable

Hello Michael, 

I have the same problem but with different structure. 

I have two tables:
Extraction table
And a date table

I want to calculate a Cumulative Sum of the business values of the pirate tickets according to the start date column.

I have a slicer that contains the sprint ID.

When the sprint ID is selected in the slicer, I'd like the abcissa axis of the graph to display all the days between the start and end of the sprint included, and the curve to represent the data.

Here's the formula I use, which unfortunately doesn't add up the data.
The formula just shows me the data per day.
I'd like to accumulate this data per day.


RunningTotalePirate =
Var Sprintstart = calculate(FIRSTDATE('Extraction_fin_sprint'[DébutSprint]), ALLSELECTED ('Extraction_fin_sprint'))
Var Length = 20

Return
If (Max(TestCalendar[Date]) = Sprintstart , 0,
If (Max(TestCalendar[Date]) >= Sprintstart && Max(TestCalendar[Date])<= Sprintstart + Length,
0 + Calculate(sum(Extraction_fin_sprint[Business Value]),
Filter(allselected(Extraction_fin_sprint),
Extraction_fin_sprint[Prévu_Pirate]= "pirate"),
Extraction_fin_sprint[Start date] <= max(TestCalendar[Date])
&& Extraction_fin_sprint[Date départ] >= min(TestCalendrier[Date])
)
))

Thanks Michael for responding. 

 

My YTD Sales measure formule is this : 

 

YTD SALES$=
CALCULATE (
FACT[Sales $],
FILTER (
ALLSELECTED ( 'Business Date'[Date] ),
'Business Date'[Date] <= MAX ( 'Business Date'[Date] )
)
)

 

My Fact table granularity is at Date level. The Slicer has the month number. With all values selected in Slicer, the running Sum for YTD sales is displaying correctly where as when I select a month in the Slicer, the result is filtered to the month selected and YTD number/running sum number becomes total for the month. 

 

I used both ALL or ALL selected in the above measure; both behaves the same way. 

 

- M

Anonymous
Not applicable

@balumaran 

ALLSELECTED should not work.

 

ALL - should:

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

 

or this:

 

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

 

Also - make sure that you use a Month from your BusinessDate table in the slicer

Michael

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

- Mani

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...

Thanks Matt & Michael

Anonymous
Not applicable

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

Anonymous
Not applicable

What formula did you use for your CumulativeSales measure?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.