Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulative Sum

Hello All,

I have the below table..

Another table contain similar month column and need to generate 1 calculated column based on cumulative sum on previuos table.

the values for the calculated should be calculated like this:_

 


Month                                calculated column                     

November                              0                                             Nov  value from previous table month
December                              0+4166.67                               sum of this table Nov and Dec from previous table month

January                                  0+4166.7+4166.67                   sum of this table Dec and jan from previuos table month.
Feburary                                0+4166.7+4166.67+4166.7

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , if you have date you can do like these examples

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))


    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date])))

  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi Anonymous ,

     

    The column "Month" should contain data of different years and the data type is Date. If you create a calculated column, try the following formula:

    Calculated Column = 
CALCULATE(
  SUM(Month1[Vendor]),
  FILTER(
    Month1,
    Month1[Month]<=Month2[Month]
  )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

     

    Best Regards,
    Winniz


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , if you have date you can do like these examples

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))


    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date])))

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    The column "Month" should contain data of different years and the data type is Date. If you create a calculated column, try the following formula:

    Calculated Column = 
CALCULATE(
  SUM(Month1[Vendor]),
  FILTER(
    Month1,
    Month1[Month]<=Month2[Month]
  )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

     

    Best Regards,
    Winniz


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Do you have multiple years? And a Date column? Simply compare Month is not good...get a Date column, then:

     

    Cal Column =

    VAR CurrentMonth = Table[Date]
    RETURN
    SUMX(FILTER(ALL(Table[Date]),Table[Date]<=CurrentMonth),Table[Vendor])

     

    Date

    01/11/2019
    01/12/2019
    01/01/2020
    01/02/2020
    01/03/2020
    01/04/2020
    01/05/2020
    01/06/2020
    01/07/2020
    01/08/2020
    01/09/2020
    01/10/2020
    01/11/2020
    01/12/2020