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 

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

 

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

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

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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€