@cumulative sum

[neeharikathota]

Hi All , 

Iam trying to get a cumulative running  sum for the column 'SWAP Values' based on the Date column "SWAP Rate Date" . The running sum calculation should start from 'May 01 2020' till latest (today) date . 

I tried to achieve this by creating measures as below : 

Attempt 1 : 

Cumulative Sum Measure 4 = VAR MaxDate = MAX (Append2[SWAP Rate Date]) -- Saves the last visible date

RETURN

CALCULATE (

sum(Append2[SWAP Values]), -- Computes sales amount

(Append2[SWAP Rate Date]) <= MaxDate && Append2[SWAP Rate Date] >= DATE(2020,05,01) && Append2[SWAP Rate Date] <= date(2020,10,01), -- Where date is before the last visible date

ALL((Append2) -- Removes any other filters from Date

))

Attempt 2 : 

Cumultative sum Measure 3 = CALCULATE (

sum(Append2[SWAP Values]),

FILTER(ALLSELECTED(Append2[SWAP Rate Date]),Append2[SWAP Rate Date]>= DATE(2020,05,01) && Append2[SWAP Rate Date]<=DATE(2020,10,01)

))

As can be seen from the image My both attempts did not give the desired result of  achieving running sum of SWAP Values based on SWAP Rate Date . 

Note : I also tested if my filters are causing any issue and as shown in image I completely filteres only the data that is needed . 

Please suggest on where Iam going wrong . 

Tagging @Greg_Deckler ,   @PowerQueryFTW  Help needed with dax measure to calculate runningsum value against a time based value Cumulative Total  as I have seen some posts where help is provided on cumlative sum calculation . 

Thanks in advance for the help . 

[v-robertq-msft]

Hi, @neeharikathota 

According to your picture, I found that your data column [SWAP Rate Date] is not correctly sorted, which leads to you can’t achieve the cumulative sum. You can follow my steps:

1. Open power query editor, select the column you want to based on to achieve cumulative sum([SWAP Rate Date]), can click this:

2. Add an index column as the cumulative key, like this:

3. Create this measure:

Cumulative Sum =

CALCULATE(

    SUM(Append2[SWAP Values]),

FILTER(ALLSELECTED('Append2'),Append2[Index]<=MAX('Append2'[Index])))

4. Create a table chart and place columns, like this:

And you can get what you want.

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[neeharikathota]

Hi Robert , 

Thanks for your suggestions before , that helped a lot . 

Iam trying to achieve cumulative sum based on Period date thats is repeating . So my cumulative sum should be grouped based on repeating Period dates . Can you help me achieve the 'Cumulative Expected Return expected result ' . As highlightes in red .. 35499=5559+5559+24381 for 01/05/2020 . 

@v-robertq-msft 

[v-robertq-msft]

Hi, @neeharikathota 

According to your requirement, you can try this measure:

Cumulative Return expected result =

CALCULATE(

    SUM('Table'[Return]),

    FILTER(ALLSELECTED('Table'),'Table'[Period Date]<=MAX('Table'[Period Date])))

And this is my test pbix file:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/xiaoxin_qiuyunus_onmicrosoft_com/EbhFCnArjBlAqA-jc...

Best Regards,

Community Support Team _Robert Qin

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