Forum Discussion

cflynn_29's avatar
cflynn_29
Icon for Helper I rankHelper I
3 years ago

Expand Dates with Start and End Date & Create Running Total inside those Expanded Group Dates

Hi All,

 

This might be a bit long winded and complicated. But i have a table that has a row which has a:

  • Category
  • Implementation Cost (negative number)
  • Operational Savings Cost (Could be positive or negative)
  • Start Date (varies)
  • End Date (always 2050)

I want to expand each row to include every year from the Start Date - End Date ( I was able to do this) See M-code below.

 

let
AllDates = {Number.From([Start Date])..Number.From([End Date])},

StartofMonthDates =
List.Distinct(List.Transform(AllDates, each Date.StartOfYear(Date.From(_))))
in
StartofMonthDates

 

But what i want to do is to create an index inside that list for each category and then create a conditional column that at the begining of that index (0 or 1) return the Implementation Cost as the first value and then return the Operational Savings Cost. That way i cand delete the old index and create the Running Total for each group that follows the expanded dates.

 

Here's an image of the table

 

 

Any assistance would be helpful

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    think you can get the result with one step, try this code in your new step

    =Table.FromRecords(List.TransformMany(Table.ToRecords(PreviousStepName),each {0..Number.From([End Date])-Number.From([Start Date])},(x,y)=>x&[Year=Date.AddYears(x[Start Date],y),RunningTotal=x[Implementation Cost]+x[Operational Savings Cost]*y]))

    • cflynn_29's avatar
      cflynn_29
      Icon for Helper I rankHelper I
      3 years ago

      So it didnt quite work out they way it supposed to it essentally all of the Categories the same along with the Implementation Costs and Operational Savings Costs and instead of each Category stopping at 2050 it just keeps going.

       

      Do i add your code after my Previous Expanded Dates code or get rid of that and use what you have?

      //My Previous Steps

      AllDates = {Number.From([Start Date])..Number.From([End Date])},

      StartofMonthDates =
      List.Distinct(List.Transform(AllDates, each Date.StartOfYear(Date.From(_))))
      in
      StartofMonthDates),

      //Your Code
      #"Expanded Dates1" = Table.ExpandListColumn(#"Added Custom4", "Dates"),
      Custom1 = Table.FromRecords(List.TransformMany(Table.ToRecords(#"Expanded Dates1"),each {0..Number.From([End Date])-Number.From([Start Date])},(x,y)=>x&[Year=Date.AddYears(x[Start Date],y),RunningTotal=x[Estimated Implementation Cost]+x[Operational Cost Saving]*y]))
      in
      Custom1

       

      I isolated one category because it should look something like this and then repeat for every category. The Custom column is 

      = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then [Estimated Implementation Cost] else [Operational Cost Saving]) and then i use the index to create a running total.

       

      Then repeat for each category

       

      Much Appreciated