Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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

Hi, @Anonymous 

Thank you for your feedback.

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]))))

 

 

 

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos!!

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.


Click here and Go to My LinkedIn Page


View solution in original post

7 REPLIES 7
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

Please try to write your measure with Filter  and && function.

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

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.


Click here and Go to My LinkedIn Page


Anonymous
Not applicable

Thank you for your reply.

 

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

Hi, @Anonymous 

Thank you for your information.

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.


Click here and Go to My LinkedIn Page


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€

 

Hi, @Anonymous 

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

 

 Picture2.png

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]))))
 
 
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

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.


Click here and Go to My LinkedIn Page


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? 

Hi, @Anonymous 

Thank you for your feedback.

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]))))

 

 

 

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos!!

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.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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