Please Help - Is there a DAX or M code for this????

[CameronKudos]

Hi guys,  This is one killing me.
How do I subtract "Daiy_Rate_Based_On_Time_Remaining" from the first row of "Total Remaining Cost" and then convert "Total Remaing Cost" into a decreasing cumulative total (subtracting the "Daily_Rate_Based_On_Time_Remaining" column each time)?

So I want the column to look like this:

8,791.71

8,765.23

8.738.75

8,712.27

etc

Here's a picture of the table. DAX or M is fine.

[ryan_mayu]

@CameronKudos 

maybe you can try this

Column = 'Table (2)'[Total_Remaining_Cost]-SUMX(FILTER('Table (2)' ,'Table (2)'[day]<=EARLIER('Table (2)'[day])),'Table (2)'[Daily_Rate])

pls see the attachment below

[CameronKudos]

Thank you so much for taking the time to do that. Unfortunately it doesn't appear to be working, no doubt because I have done something wrong.

Here's what I wrote in:

Funds Remaining = 'Future Date Service Agreement Plans (3)'[Total_Remaining_Cost__c] -SUMX(FILTER('Future Date Service Agreement Plans (3)' , 'Future Date Service Agreement Plans (3)'[Day By Day]<=EARLIER('Future Date Service Agreement Plans (3)'[Day By Day])),'Future Date Service Agreement Plans (3)'[Daily_Rate_Based_On_Time_Remaining__c])

Not sure if it makes a difference, but there are multiple account ID's. The 'Day By Day' column list each date from today's date until the end of the clients service agreement with us. Each time the rows reach a new account ID, the 'Day By Day' column resets back to todays date and goes through the the process again.

[ryan_mayu]

@CameronKudos 

I guess you want to substract one month's cumulative value?

could you pls simplified the sample data and also provde the expected output as well. 

Pls also clarity and provide the detailed calculation logic.

[CameronKudos]

Hi again, so I actually need this to be in M. Is that possible?

I desperately need to figure this out for my work.

Here's the overview:

I have a table , named 'Future Date Service Agreement Plans (3)' with two columns that are giving me trouble. They are called:

[Total_Remaining_Cost__c] 

and 

[Daily_Rate_Based_On_Days_Remaining__c]

Both of these columns repeat the same number over and over again until another dimension in the table [Account Name] changes, then they repeat the process. 

Would I would like for them to do is subtract the [Daily_Rate_Based_On_Days_Remaining__c] from the [Total_Remaining_Cost__c]  and the have the [Total_Remaining_Cost__c] column go down with each day. So, it would look just as yours did at the start, only it would need to start over with each [Account ID] change (Which happens when the date in the Day By Day column reaches the date in the EndDate column.

Here's a picture of what the table currently looks like.

[Anonymous · Accepted solution]

Hi @CameronKudos ,

You can refer the part 3: Group conditional accumulation of the following blog to achieve it with Power Query:

Grouped Running Totals in Power Query

Accumulative, conditional accumulative in Power Query

Best Regards