Forum Discussion
Please Help - Is there a DAX or M code for this????
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.
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
5 Replies
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
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.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.
