M or DAX or neither - row-wise calculations with lags

[karen578]

https://docs.google.com/spreadsheets/d/1wq7lKXy4L9OUN8O6cifxsPRTVTfwe57F/edit?usp=sharing&ouid=10054...

Hi - new to Power BI and would love some help conceptualizing my problem. My company has a model that's currently way too big for Excel and we're looking for alternatives. The final results will be in Power BI dashboards, so I'm curious whether the data can be generated within Power BI as well. Looking for a general short answer here - would rather get a quick answer than nothing and I realize this is a pretty broad question...

The model takes existing historical data and projects it into the future, and I'm having troubles conceptualizing how to generate the future (unknown) data. Specifically with how to create data in each row that references other rows.

An overly simplified example picture is below. We have 12 month accounts that renew annually, and we want to project the total accounts. Retention rates (# renewing / # written at the same time last year) and new business numbers are given assumptions. Let's just assume there are no cancellations, so the total accounts this month = the total accounts last month + new accounts + renewed accounts - total accounts that expired this month / were up for renewal. This is incredibly easy in Excel, but I can't figure it out in Power BI.

My thoughts so far:

1. This is an ETL process and probably better suited to Power Query over calculated columns in DAX.

2. Lag calculations are hard in Power Query, and not really workable here. I basically need to iterate over the rows of the projection data (for loop) one at a time to build up the data, whereas Power Query is built for calculations 1 column at a time. Joining data to itself with the help of index, lag 1 index, and lag 2 index columns wouldn't help me here beyond the first row of the projection data. And that is not a process that should be repeated for every row in the projection data.

3. Some variation of the accumulate function seems like it could work - it would get the lagged total fine, but what about the lag 12 data for the upcoming renewals?

4. I could use R or Python scripts to do this in Power Query, but that seems a bit excessive/ possibly slow. It's just a for loop needed here, nothing specific to either of those languages.

5. Alternatively, this probably could be a calculated column in DAX though I know that's not the intended use of DAX. If I were to go down that route, I think I would have to change the definition of the underlying tables, so that fact tables don't have connections to other fact tables.

• The current model has a fact tables for Number of Accounts and Revenue. The projection process for Revenue looks at projected average account revenue * Number of Accounts
• If all the processing was done in Power Query, I was thinking I could keep this format. But if I do it in DAX, it would be better to use an average account revenue fact table, instead of total revenue, right?

Other notes: this projection process happens for many combinations of business units and provinces. I saw that I could apply calculations to nested tables of grouped data in Power Query - but that runs the risk of being very slow? Couldn't find too much information on how to do that.

Any thoughts or ideas? Thanks in advance for helping a noobie!

[lbendlin]

Not so sure about the "cleaner"  part but if it works for you then that is good.  In general Power Query does not like recursions too much as you risk exhausting the available memory when you lug your data through the iterations and recursion levels.  List.Accumulate stays onedimensional compared to recursions that go multidimensional by design. 

[karen578]

hi @lbendlin, thanks for your reply. I can see what you mean that DAX wouldn't make sense, but thanks for providing that opinion. I've updated my post with data in 3 tables - the known data, the assumptions, and the combined final expected result. 

I'll explain the total value of 11267 for 202207: 11023 + 679 + 10675 (11120 * 0.96) - 11120 (556 + 10564 from 202107). I haven't been able to find much documentation on List.Accumulate, especially when multiple fields are involved. I'd love to see an example of how this can be done.

I'm used to other tools (primarily R) where this would be a good data structure. In simple English (not M), I think of this as the following. Not sure if this is as helpful as I know I'll have to start adjusting my thinking to work with M functions.

1. Append assumption data to known data (2 datasets to 1, longer)

2. Sort by year month

3. Group by ID

4. Modify Up for Renewal: if Up for Renewal = null, lag(New + Renewal, 12), else Up for Renewal

5. Renewal = if Renewal = null, Up for Renewal * retention

6. Total = lag(Total, 1) + New + Renewal - Up for Renewal

[lbendlin]

Everything is implemented except step 6.  That requires a List.Accumulate.

let
    Source = #"Known Data" & Assumptions,
    #"Sorted Rows" = Table.Sort(Source,{{"Line", Order.Ascending}, {"Year Month", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Up for Renewal],each if [Up for Renewal] = null then #"Added Index"[New Accounts]{[Index]-12} + #"Added Index"[Renewal Accounts]{[Index]-12} else [Up for Renewal],Replacer.ReplaceValue,{"Up for Renewal"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Renewal Accounts],each if [Renewal Accounts] = null then [Up for Renewal] * [Retention Rate] else [Renewal Accounts],Replacer.ReplaceValue,{"Renewal Accounts"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [Total Active Accounts],each if [Total Active Accounts] = null then #"Added Index"[Total Active Accounts]{[Index]-1}+[New Accounts]+[Renewal Accounts]-[Up for Renewal] else [Total Active Accounts],Replacer.ReplaceValue,{"Total Active Accounts"})
in
    #"Replaced Value2"

[lbendlin]

Here is the version including List.Accumulate.  Note that the numbers ever so slightly differ from yours. Please check the formulas.

let
  Source = #"Known Data" & Assumptions,
  #"Sorted Rows" = Table.Sort(Source, {{"Line", Order.Ascending}, {"Year Month", Order.Ascending}}),
  #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
  #"Replaced Value" = Table.ReplaceValue(
    #"Added Index",
    each [Up for Renewal],
    each
      if [Up for Renewal] = null then
        #"Added Index"[New Accounts]{[Index] - 12} + #"Added Index"[Renewal Accounts]{[Index] - 12}
      else
        [Up for Renewal],
    Replacer.ReplaceValue,
    {"Up for Renewal"}
  ),
  #"Replaced Value1" = Table.ReplaceValue(
    #"Replaced Value",
    each [Renewal Accounts],
    each
      if [Renewal Accounts] = null then
        Number.RoundDown([Up for Renewal] * [Retention Rate])
      else
        [Renewal Accounts],
    Replacer.ReplaceValue,
    {"Renewal Accounts"}
  ),
  cumul = (s, i) =>
    List.Accumulate(
      {s .. i},
      0,
      (state, current) =>
        state
          + #"Replaced Value1"[New Accounts]{current}
          + #"Replaced Value1"[Renewal Accounts]{current}
          - #"Replaced Value1"[Up for Renewal]{current}
    ),
  #"Replaced Value2" = Table.ReplaceValue(
    #"Replaced Value1",
    each [Total Active Accounts],
    each
      if [Total Active Accounts] = null then
        let
          start = 12 * Number.RoundDown([Index] / 12)
        in
          #"Replaced Value1"[Total Active Accounts]{start - 1} + cumul(start, [Index])
      else
        [Total Active Accounts],
    Replacer.ReplaceValue,
    {"Total Active Accounts"}
  )
in
  #"Replaced Value2"

[karen578]

Thanks for showing me this! It really helps to see examples of the accumulator function accessing different fields. I wouldn't have been able to write that myself. I also wouldn't have thought of how to do this without a group by. 🙂

But what if I had to extend this beyond 12 projection periods, say for 202307? The first replacement step for Up for Renewals won't work because the replacement value of 12 back for Renewals won't be calculated yet and still null. I'm not sure how to change this. Is it possible to somehow group the 3 replacement steps together so all 3 values on a row are calculated before moving on to the next row? I can only think to break the projection period (in the Assumptions table) into multiple tables spanning 1 year each, and then applying these steps recursively.

[lbendlin]

Did I mention that List.Accumulate is extremely powerful?  This is just a first taste of what it can do.  It can do much crazier things, like selective accumulation and in-list shifting.

But I also want to give a shout-out to Table.ReplaceValue .  As you may have noticed I am using it in a slightly unorthodox way.  It too has a special feature that allows you to do much crazier things.

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[karen578]

Thanks @lbendlin, I appreciate your patience. I've updated the link to the data and expected results. I just added 4 rows to the Assumptions table (2 for 202307 and 202308 for groups A & B), added in your Power Query queries, and changed the start variable in the #"Replaced Value2" step to 26 * Number.RoundDown([Index]/26) + 12 to account for the extra records in Assumptions. The problem with the solution is that the #"Replaced Value" step doesn't work when the forecasted period is more than 12 periods - because the Table.ReplaceValue function as it's being used looks 12 cells back and 12 cells back is null after 12 periods. My fault completely for not providing a good example. 🙂

[lbendlin · Accepted solution]

I believe your new example will not be implementable like that in Power Query as you are trying to write into and read from a table cell in the same step (202307 is dependent on 202207 which itself is not filled at the beginning of processing. You will need to do it in steps of 12, and run multiple steps.

Something like this

let
    Source = #"Known Data" & Assumptions,
    #"Sorted Rows" = Table.Sort(Source,{{"Line", Order.Ascending}, {"Year Month", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Up for Renewal],each if [Up for Renewal] = null then #"Added Index"[New Accounts]{[Index]-12} + #"Added Index"[Renewal Accounts]{[Index]-12} else [Up for Renewal],Replacer.ReplaceValue,{"Up for Renewal"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Renewal Accounts],each if [Renewal Accounts] = null then Number.RoundDown([Up for Renewal] * [Retention Rate]) else [Renewal Accounts],Replacer.ReplaceValue,{"Renewal Accounts"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [Up for Renewal],each if [Up for Renewal] = null then #"Replaced Value1"[New Accounts]{[Index]-12} + #"Replaced Value1"[Renewal Accounts]{[Index]-12} else [Up for Renewal],Replacer.ReplaceValue,{"Up for Renewal"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",each [Renewal Accounts],each if [Renewal Accounts] = null then Number.RoundDown([Up for Renewal] * [Retention Rate]) else [Renewal Accounts],Replacer.ReplaceValue,{"Renewal Accounts"}),
    cumul = (s,i) => List.Accumulate({s..i},0,(state,current)=> state + #"Replaced Value3"[New Accounts]{current} + #"Replaced Value3"[Renewal Accounts]{current} - #"Replaced Value3"[Up for Renewal]{current}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",each [Total Active Accounts],each if [Total Active Accounts] = null then 
    let
       start = if [Index]<27 then 12 else 38
    in
       #"Replaced Value3"[Total Active Accounts]{start-1} + cumul(start,[Index])
    else [Total Active Accounts],Replacer.ReplaceValue,{"Total Active Accounts"})
in
    #"Replaced Value4"

The "only" problem is your new ragged segmentation for each line. Instead of 12/12 you now have 12/14, and I feel that you want to make this even more dynamic.  At this point it will be easier to look for a proper planning tool, rahter than trying to abuse a reporting tool like Power BI.

[karen578]

Thanks for all of your help @lbendlin. This is what I originally expected was the case, but I really appreciate you providing worked-out sample code. It's helped me learn more about indexing and accumulating in Power BI.

It's a shame that it's so easy to do this in Excel and so hard in Power Query, but I get it. I realize that Power BI is not built to do this kind of thing and was never going to be our primary tool. We're using it to create dashboards off of the results of our models. I was only looking into this to see how feasible it would be to re-run some of the main assumptions for scenario testing in Power BI.

[karen578]

Hey @lbendlin, not sure if you'd be interested in looking at this at all, but I figured out a solution that's much cleaner. I didn't realize we could create custom recursive functions in M. This has been a very good introduction to writing M, thanks again for your help.

First I created a new function ProjectRows that loops over the Assumptions table recursively:

= (known as table, assumptions as table, loop as number) =>
//loop through all records in the assumptions table, adding one record at a time to the known table
if loop >= Table.RowCount(assumptions) //stop once every assumptions record has been accounted for
then known
else
let last12 = Table.LastN(known, 12),
up_renewal = last12{0}[New Accounts] + last12{0}[Renewal Accounts],
//create a new record from the current assumptions record - and add calculated fields to it
new_record = assumptions{loop},
new_record1 = Record.AddField(new_record, "Up for Renewal", up_renewal),
new_record2 = Record.AddField(new_record1, "Renewal Accounts", up_renewal * new_record[Retention Rate]),
new_record3 = Record.AddField(new_record2, "Total Active Accounts", last12{11}[Total Active Accounts] + new_record2[New Accounts] + new_record2[Renewal Accounts] - new_record2[Up for Renewal]),
//add the new projected record to the end of the table
new_table = Table.InsertRows(known, Table.RowCount(known), {new_record3})
//call this function recursively until all assumptions are added
in @ProjectRows(new_table, assumptions, loop+1)

I added a step to both the Known and Assumptions data tables to group by [Line] and keep all rows. I merged Known and Assumptions on [Line] (1 table, 2 records, fields Line, Known, and Assumptions - with the details for Known and Assumptions stored in nested tables).

I then added a added a custom column, calling ProjectRows([Known], [Assumed], 0) on each row. This ensures only the right group records are contemplated, and the counter starts at 0 and ends at the last row in [Assumptions] for each group. End result is another nested table containing the full known and projected date range - then it's just a matter of deleting the separate [Known] and [Assumption] columns, and expanding all fields on the [Projected] column.

#"Grouped Rows" = Table.Group(Known, {"Line"}, {{"Known", each _, type table [Line=nullable text, Year Month=nullable text, Total Active Accounts=nullable number, New Accounts=nullable number, Renewal Accounts=nullable number, Up for Renewal=nullable number]}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Line"}, Assumptions, {"Line"}, "Assumptions", JoinKind.LeftOuter),
#"Expanded Assumptions" = Table.ExpandTableColumn(#"Merged Queries", "Assumptions", {"Assumptions"}, {"Assumptions"}),
#"Invoked Custom Function" = Table.AddColumn(#"Expanded Assumptions", "Projected", each ProjectRows([Known], [Assumptions], 0)),
#"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Line", "Known", "Assumptions"}),
#"Expanded Projected" = Table.ExpandTableColumn(#"Removed Columns", "Projected", {"Line", "Year Month", "Total Active Accounts", "New Accounts", "Renewal Accounts", "Up for Renewal"}, {"Projected.Line", "Projected.Year Month", "Projected.Total Active Accounts", "Projected.New Accounts", "Projected.Renewal Accounts", "Projected.Up for Renewal"})

[lbendlin]

"I'm curious whether the data can be generated within Power BI as well"

Only if the data generation can be automated. Power BI is notoriously bad at accepting user input. It has no memory and few data write back capabilities

On the other hand List.Accumulate() is extremely powerful and can easily do these lag computations. Performance might be a concern if you have more than a couple hundred rows.  Good data preparation is key (unpivoting your data among the most needed steps).  

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523