cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Cumulative sum by taking into account slicers

Dear,

My data:

Here is my issue, I have a table ['MERGED-COST SAVINGS'] with differents columns (i.e granularity) and i want to RETURN a colum G with the cumulative sum of the savings (Column F) in function of the date (Column E)

I wrote this formula:

Using this, I got the wanted graph, see below:

However, when I'm applying any slicer on this graph, for example by SITE or TYPE or SAVING IDEA,

then the curves and the amount ARE NO LONGER CORRECT.

How can I still have my graphs and amount correct AFTER applying any slicer ?

Thanks in advance for your support !

1 ACCEPTED SOLUTION
Super User

Well, this not Excel.
Assuming that start date is NOT in a relationship with a date dimension table, try this:

``````Cumulative Sum = VAR ed=MAX('MERGED-COST SAVINGS'[Start date])
RETURN CALCULATE(SUM('MERGED-COST SAVINGS'[Full Year Forecast 2024]), ALL('MERGED-COST SAVINGS'[Start date]), 'MERGED-COST SAVINGS'[Start date]<=ed)``````

7 REPLIES 7
Super User

Forget about previous rows, this is not Excel. Cummulative values we do all the time in Power BI. As I do not have your pbix it is hard to guess what;s going on, but if you're using a date dimension in your visual that is filtering that fact table you should use correct the measure to use the date dimension.

Super User

Well, this not Excel.
Assuming that start date is NOT in a relationship with a date dimension table, try this:

``````Cumulative Sum = VAR ed=MAX('MERGED-COST SAVINGS'[Start date])
RETURN CALCULATE(SUM('MERGED-COST SAVINGS'[Full Year Forecast 2024]), ALL('MERGED-COST SAVINGS'[Start date]), 'MERGED-COST SAVINGS'[Start date]<=ed)``````

Helper I

Dear,

Thx for your reply. However, I still have the same results than the measure I've posted.

My goal is to create a physical colum on Dax based on Excel example here below:

By doing so, I would have a colum, which a each step (each incremented date), I have the cummulative sum.

Then, I would just need to plot such colum and I will have my raising curve in fct of time.

Super User

My guess is that you've created a computed column where you should create a measure. Computed columns are calculated when loading the model and are never affected by filters & slicers....

Helper I

Dear,

I did create a measure, see screenchot.

Super User

Ah OK.
The problem in your measure is most likely the ALL() function overriding all filters, you would want to override only the start date context.

Helper I

Basicaly,

I want to create (add) a column such as below:

This is easy to do on Excel:

for a date (t), I sum its corresponding amount + the amount on date (t-1)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors