Create a cumulative sum colum in function of date (sorted from oldest to most recent)

[Yazid]

Dear,

 I want to create (i.e add) a column G such as below:

This is easy to do on Excel:

for a date (t), I sum its corresponding amount + the amount on date (t-1)

How Can I create such function on PowerBi (DAX) ?

Thanks in advance for your support !

[Anonymous · Accepted solution]

Hi @Yazid ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

MEASURE =
VAR tmp =
    WINDOW ( 1, ABS, 0, REL, ALLSELECTED ( 'Table'[Date], 'Table'[Forecast] ) )
RETURN
    SUMX ( tmp, [Forecast] )

3. add a table visual with field and measure

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ray_Minds]

Did I answer your question? Mark my post as a solution! This will help others on the forum!

[Ashish_Mathur · Accepted solution]

Hi,

Once you have a Calendar Table, this measure should work

Total = sum(Data[Amount])

Total YTD = calculate([Total],datesytd(calendar[date],"31/12"))

Hope this helps.

[Anonymous · Accepted solution]

Hi @Yazid ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

MEASURE =
VAR tmp =
    WINDOW ( 1, ABS, 0, REL, ALLSELECTED ( 'Table'[Date], 'Table'[Forecast] ) )
RETURN
    SUMX ( tmp, [Forecast] )

3. add a table visual with field and measure

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.