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

Anonymous
Not applicable

## cumulative sum with before date filter

Hello,

I am having an issue that I can't seem to figur out.

I need a cumulative SUM but only since a certain date.

Calculated Column= CALCULATE(SUM(Column), ALL('Table'),'Table'[Date]<= EARLIER('Table'[Date]))

I need to add another filter that only does this calculated column when date < 2021/01/01

How can this be done?

Thanks a lot!
1 ACCEPTED SOLUTION
Super User

Hi, @Anonymous

In my opinion, Calculated Column and Calculated Measure works slightly differently.

I don't think we can use the same DAX for column creation and for visualization creation.

The below is for the table visualization.

Calculated Measure =
IF( MAX('Table'[Date]) >= DATE(2021,1,1), BLANK(),
CALCULATE( SUM('Table'[Cost]), FILTER( ALL('Table'), 'Table'[Date] <= MAX( 'Table'[Date]))))

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

7 REPLIES 7
Super User

Hi, @Anonymous

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Anonymous
Not applicable

If i do this calculated column:

Calculated Column = CALCULATE(SUM('Table'[Column]), FILTER(ALL('Table'), 'Table'[Date]<=EARLIER('Table'[Date]) && 'Table'[Date]<DATE(2021,1,1)))

It continues giving me the cumulative sum but continues after the 2021,1,1, date, when supposedly i filtered it to stop at 2021,1,1

Super User

Hi, @Anonymous

Is it continuously giving the same number after 2021.1.1 ?

If it is OK with you, please kindly share the sample data, then I can try to see whether I can write a measure.

Thank you very much.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Anonymous
Not applicable

Yes, it gives the same value after 2021,1,1

I am unable, for some reason, to input here a proper table, so I have to do it like this:

Date                        Cost       Cumulative value 2021

2020/01/01            1000€        1000€

2020/02/02            1000€         2000€

2021/01/01            2000€

Super User

Hi, @Anonymous

I created the sample data, and please check the below picture.

Please kindly check if this is similar to what you are looking for.

Calculated Column = IF( 'Table'[Date]>=DATE(2021,1,1), BLANK(), CALCULATE(SUM('Table'[Cost]), FILTER(ALL('Table'), 'Table'[Date]<=EARLIER('Table'[Date]))))

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Anonymous
Not applicable

Thank you!
That does work for the calculated column 🙂

But if I do a table chart, i am unable to get it right.

If I have month and year (or just month) on the first column, I get a lot of repeated values.

Like a bunch of "February" with the same cost.

Do you know why this happens?

Super User

Hi, @Anonymous

In my opinion, Calculated Column and Calculated Measure works slightly differently.

I don't think we can use the same DAX for column creation and for visualization creation.

The below is for the table visualization.

Calculated Measure =
IF( MAX('Table'[Date]) >= DATE(2021,1,1), BLANK(),
CALCULATE( SUM('Table'[Cost]), FILTER( ALL('Table'), 'Table'[Date] <= MAX( 'Table'[Date]))))

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.