Can this even be done in M??? I'm seriously stuck

[CameronKudos]

Hi all,

I desperately need to figure this out for my work.

Here's the sample data: https://drive.google.com/file/d/1dI7COyElnNXb33iSk1eiau8ESONHLphf/view?usp=sharing

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 like this:

But 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.

[KNP · Accepted solution]

@CameronKudos - I may be misunderstanding but, have a look at the 'Custom' column in the attached PBIX. (be sure to provide the sample file next time, I had to recreate the file manually 😉) you'll need to repoint your Source step back to your file path.

Here's the code if you prefer...

let
  Source = Excel.Workbook(
    File.Contents("H:\My Drive\Power BI\Community Solutions\Files\Future Revenue Data.xlsx"),
    null,
    true
  ),
  Future_Revenue_Table = Source{[Item = "Future_Revenue", Kind = "Table"]}[Data],
  #"Changed Type" = Table.TransformColumnTypes(
    Future_Revenue_Table,
    {
      {"AccountId", type text},
      {"Day By Day", type date},
      {" EndDate", type date},
      {" Days Remaining", Int64.Type},
      {" Days in Month", Int64.Type},
      {" Months Remaining", Int64.Type},
      {" Days Left In Month", Int64.Type},
      {" Daily_Rate_Based_On_Days_Remaining__c", type number},
      {" Total_Remaining_Cost__c", type number},
      {"Funds_Allocated_Per_Month__c", type number}
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Custom",
    each [#" Total_Remaining_Cost__c"]
      - (
        [#" Total_Remaining_Cost__c"]
          - [#" Days Remaining"]
          * [#" Daily_Rate_Based_On_Days_Remaining__c"]
      )
  )
in
  #"Added Custom"

[CameronKudos]

Thank you so much for thaking the time to do this!

[ronrsnfld]

I can't download your example (request access message when I tried), but one way to do this is by using the Table.Group function, and writing a custom aggregation that does the "running total" for each subgroup. You need to get into the Advanced Editor to do this.

[CameronKudos]

Hi, I have changed the permissions now, sorry about that. I have come up with one solution similar to what you've said, but it slows everything down dratiscally, so if you do know a way to do it quicker, I'd be greatly appreciative of you.

[ronrsnfld]

There are slow and more rapid ways of doing the running totals.

Your download didn't have a raw data table I could use, but I was able to copy the data table shown in the report into an Excel workbook and apply power query there.

The code should work in either.

I was not sure, from your description, exactly what you were grouping on, so I chose to group on just the Account ID column, but a different algorithm could certainly be used.

The additional column is added as the last column to your table, but you can certainly move it around.

I use List.Generate to generate the running total (as a custom aggregation in Table.Group), and then add that column to the original grouped subtable.  Let me know if you have trouble adapting it, and how it works out.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AccountId", type text}, {"Day By Day", type date}, 
        {" EndDate", type date}, {" Days Remaining", Int64.Type},{" Days in Month", Int64.Type}, 
        {" Months Remaining", Int64.Type}, {" Days Left In Month", Int64.Type}, 
        {" Daily_Rate_Based_On_Days_Remaining__c", type number}, {" Total_Remaining_Cost__c", type number}, 
        {"Funds_Allocated_Per_Month__c", type number}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"AccountId"}, {

    //create list of running totals (subtractions)
    //then add it as a column to original table
        {"Column", (t)=> 
            Table.FromColumns(
                Table.ToColumns(t) & {        
                    List.Generate(
            ()=>[rt=t[#" Total_Remaining_Cost__c"]{0} - t[#" Daily_Rate_Based_On_Days_Remaining__c"]{0}, idx=0],
            each [idx] < Table.RowCount(t),
            each [rt= [rt] - t[#" Daily_Rate_Based_On_Days_Remaining__c"]{[idx]+1}, idx=[idx]+1],
            each [rt])},type table [
                AccountId=nullable text, Day By Day=nullable date, #" EndDate"=nullable date, #" Days Remaining"=nullable number, 
                #" Days in Month"=nullable number, #" Months Remaining"=nullable number, #" Days Left In Month"=nullable number, 
                #" Daily_Rate_Based_On_Days_Remaining__c"=nullable number, #" Total_Remaining_Cost__c"=nullable number, 
                Funds_Allocated_Per_Month__c=nullable number,Column=nullable number
                    ]), type table
            
            }
        }),
    #"Expanded Column" = Table.ExpandTableColumn(#"Grouped Rows", "Column", {"Day By Day", " EndDate", " Days Remaining", " Days in Month", " Months Remaining", " Days Left In Month", " Daily_Rate_Based_On_Days_Remaining__c", " Total_Remaining_Cost__c", "Funds_Allocated_Per_Month__c", "Column"}, {"Day By Day", " EndDate", " Days Remaining", " Days in Month", " Months Remaining", " Days Left In Month", " Daily_Rate_Based_On_Days_Remaining__c", " Total_Remaining_Cost__c", "Funds_Allocated_Per_Month__c", "Column.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Column",{{"AccountId", type text}, {"Day By Day", type date}, {" EndDate", type date}, {" Days Remaining", Int64.Type}, {" Days in Month", Int64.Type}, {" Months Remaining", Int64.Type}, {" Days Left In Month", Int64.Type}, {" Daily_Rate_Based_On_Days_Remaining__c", type number}, {" Total_Remaining_Cost__c", type number}, {"Funds_Allocated_Per_Month__c", type number}, {"Column.1", type number}})
in
    #"Changed Type1"

[CameronKudos]

Hi Pat, no problem, but how do I attach the .pbix file? I've tried excel ones too with no luck. I can't paste the sample here because you need 399 rows to see what happens when AccountID changes.

[CameronKudos]

Here you go:

https://drive.google.com/file/d/1dI7COyElnNXb33iSk1eiau8ESONHLphf/view?usp=sharing

[mahoneypat]

@CameronKudos I requested access. FYI that you need to set it to "anyone with the link" so that anyone in the community could help/respond.

Pat

[CameronKudos]

You should have permission now. I've figured one way of doing it, but it slows everything down ridiculously

[mahoneypat]

See attached file for one way to do it. If the first is too slow, you can try the "WithBuffer" option. I would typically do this calculation with a measure, but you specified you wanted it in M. Note that I had to export your data as a csv with DAX studio and bring it in so I could modify the query.

Pat

[CameronKudos]

Thank you so much for thaking the time to do this!

[mahoneypat]

People usually upload it to Google Drive, OneDrive, or some other fileshare and provide a link.

Pat

[mahoneypat]

This is doable, but please paste some sample data as a table and not as an image. It will make it much easier for someone to provide a specific solution.

Pat