Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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]<= ...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

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

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

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]))))
 
 
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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? 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

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