Forum Discussion

CameronKudos's avatar
CameronKudos
Helper I
4 years ago
Solved

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 cumulativ...
Need Help
ryan_mayu's avatar
ryan_mayu
Super User
4 years ago

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's avatar
    CameronKudos
    Helper I
    4 years ago

    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's avatar
      ryan_mayu
      Super User
      4 years ago

      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's avatar
        CameronKudos
        Helper I
        4 years ago

        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.