Power BI Cumulative sum by date and by category issue

[NeimadB]

Hi there,

I'm facing a problem with a DAX formula that  compute a cumulative SUM for my sales of cycles.

I got a simple dataset SalesByCycle, with the following columns :

• Date
• Cycle
• Sales

Date is Date column

Cycle is a category of our products

Sales is the amount sold

I got a Date slicer, and a Cycle Slicer that let me chose 1 or several Cycles.

The problem is : the categorization of the cumulative SUM doesn't work when I select more than one cycle.

Here is the result when I select only one cycle (cycle 1 there) : everything is fine

Abd the result when I select all cycles (or 2 or 3) : the provessive SUM doesn't work (each Cycle that have data on this day have the same value : the grand total of the cumulation at this date)

Here is the DAX code of my cumulative SUM :

ActualSalesByCycle =
var DateValue = MAX(SalesByCycle[Date])
var CyclesValues = ALLSELECTED(SalesByCycle[Cycle])
RETURN
CALCULATE( SUM(SalesByCycle[Sales]), FILTER(ALL(SalesByCycle), SalesByCycle[Date] <= DateValue && SalesByCycle[Cycle] IN CyclesValues ) )

Thank you

[v-jianboli-msft · Accepted solution]

Hi @NeimadB ,

Here is my sample:

Please try:

ActualSalesByCycle = 
var DateValue = MAX(SalesByCycle[Date])
RETURN
CALCULATE( SUM(SalesByCycle[Sales]), FILTER(ALL(SalesByCycle), SalesByCycle[Date] <= DateValue && SalesByCycle[Cycle] =SELECTEDVALUE(SalesByCycle[Cycle]) ) )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.