Reverse Cumulative sum but as a calculated column

[Birinder]

Hi Community.

I am having trouble in creating a calculated column for reverse cumulative sum for one of my table.
I am using the measure but It doesn't seem much flexible.
Here is the measure, I am using:-

Measure Date = calculate(SUM('Full BECF'[USD]),FILTER(ALLSELECTED('Full BECF'[Date]),'Full BECF'[Date]>=selectedvalue('Full BECF'[Date])))

It is working fine as expected.
Below is a screenshot of the sample data

Can I get the exact same values but as the calculated column.
Thanks a lot for the help in Advance.
Peace.

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a new column.

Expected result CC =
SUMX (
    WINDOW (
        1,
        ABS,
        0,
        REL,
        SUMMARIZE ( 'Full BECF', 'Full BECF'[Date], 'Full BECF'[USD] ),
        ORDERBY ( 'Full BECF'[Date], DESC )
    ),
    'Full BECF'[USD]
)

[Birinder]

@JihwanKim 
Many thanks for the reply.
Unfortunately, WINDOWS function is not working for me.
It's not like the results are not coming as I want, It's that DAX is not recognizing it as function.

[Anonymous]

Try this:

ReverseCumulativeSum = 
SUMX(
    FILTER(
        ALL('Full BECF'),
        'Full BECF'[Date] >= EARLIER('Full BECF'[Date]
    ),
    'Full BECF'[USD]
)

If you don't want to use "EARLIER", you could store the date on a variable and then use it. For simiplicity, I avoided using variables and return statements.

[Birinder]

Hi @Anonymous 

Thanks for the reply.
I am using it and it is working fine when we have only two rows i.e. date and amount.
I am trying to add extra row and then results are going crazy.

AS you can see that total of USD is appearing as first value for the calculated column.
I guess it is calculating it on the basis of date only.
Is there a way by I can add one or more than one extra row alongwith date column and it gives me the values as per the extra column and also on the basis of date.
If it is sounding confusing here is the example generated from the same measure.

Many thanks in Advance for help.

[Anonymous]

😀You should see this post 

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447595#M6071...

You should always give complete information. In your post, you said the data model has two columns and you wanted a calculated column. I gave the DAX for that.

Please post what are the tables, fileds and relationships between them and what exactly is the calculation that you need. Is it a measure that you want to use it in visuals or a calculated column on a table? Otherwise you will end up with getting incorrect DAX codes from the forum. Not only me, anyone who wants to help need all these information.

[Birinder]

Hi @Anonymous 
Thanks for your reply.
I apologize but Its not that I am blaming you for this.
I actually needed DAX for two rows only.
Upon further granular analysis, I thought of using the other row too.

Anyway.
I will try to provide the full information.
There is only one table named as " Full BECF".
No relationship as there is only one table.
5 column in the table.

• Source -  Used as a filter.
• Stage - Used as a filter
• Key- Used a 1st place in row section
• Date - Used as 2nd place in row section 
• USD - Used as value

I want the results as a calculated column.

Now I will try to explain you what I need.

I want reverse cumulative sum for the key column based on the Date column.
Just like the Screenshot below:

Thanks for the help so far,

[Anonymous · Accepted solution]

If there is only one table, and you want a calculated column, you can just add the filters for other columns also...

ReverseCumulativeSum = 
SUMX(
    FILTER(
        ALL('Full BECF'),
        'Full BECF'[Date] >= EARLIER('Full BECF'[Date] &&
        'Full BECF'[Key] = EARLIER('Full BECF'[Key] &&
        'Full BECF'[Stage] = EARLIER('Full BECF'[Stage] &&
        'Full BECF'[Source] = EARLIER('Full BECF'[Source]
    ),
    'Full BECF'[USD]
)