Forum Discussion

AndrejZitnay's avatar
AndrejZitnay
Post Patron
5 years ago
Solved

Model calculation

Hello all,

 

Would you be so kind and help me out?

I am working on selling scenario where campaign is for 5 years.

 

(In attached example it is two years Mar-21 to Feb-23)

 

There is given intake for every month.

 

For exapmle in Model A for Feb-22 we have intake of 1354 but that will converte to outcome between 1-18 months.

 

1.35 will convert in Mth 1 (Feb-22), 4.74 will convert in Mth2 (March-22) etc.

 

In my real scenario I have 3 models.

3 different percentage splits. (Speed of conversion rate differs in each of model)

 

I can add models as tables to Power BI but I don't know how to calculate that against my intake.

I would like to show outcome against actual months not just month table.

For example Mth18 for Intake from Feb-22 will be in Jul-23.

Outcome per actual month.

 

Is there anyway to achieve that in Power BI?

My real scenario is intake for 60 months and outcome for period of 78 months

 

I could do that manually but I have 60 months x 3 model x 5 campaigns = 900 tables

It will be difficult to maintain that in excel. 

 

I want to input my models to Power BI and hopefully everything else will be calculated.

 

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    Hi AndrejZitnay ,

     

    No problem, found the issue. In Advanced Editor, make this change in your Intake query:

    //Change this:
addPayMonth = Table.AddColumn(expandModelA, "payMonth", each Date.AddMonths([intakeMonth], [payMonthNumber])),

//to this:
addPayMonth = Table.AddColumn(expandModelA, "payMonth", each Date.AddMonths([Month], [payMonthNumber])),

//[payMonth] was being calculated from [intakeMonth], but should actually be calculated from [Month]

     

     

    Optional extra:

    Update your modelA query with the following code to remove zero values and have a sharper looking table:

    let
    Source = yourSampleFile.xlsx,
    #"Model A_Sheet" = Source{[Item="Model A",Kind="Sheet"]}[Data],
    promHeads = Table.PromoteHeaders(#"Model A_Sheet", [PromoteAllScalars=true]),
    remWeight = Table.RemoveColumns(promHeads,{"Weight"}),
    repZeroNull = Table.ReplaceValue(remWeight,0,null,Replacer.ReplaceValue,{"Mth 1", "Mth 2", "Mth 3", "Mth 4", "Mth 5", "Mth 6", "Mth 7", "Mth 8", "Mth 9", "Mth 10", "Mth 11", "Mth 12", "Mth 13", "Mth 14", "Mth 15", "Mth 16", "Mth 17", "Mth 18"}),
    unpivotOtherCols = Table.UnpivotOtherColumns(repZeroNull, {"Month"}, "Attribute", "Value"),
    renCols = Table.RenameColumns(unpivotOtherCols,{{"Attribute", "payMonth"}, {"Value", "paySplit"}, {"Month", "modelMonth"}}),
    addModelMonthNumber = Table.AddColumn(renCols, "payMonthNumber", each Text.AfterDelimiter([payMonth], " "), type text),
    chgAllTypes = Table.TransformColumnTypes(addModelMonthNumber,{{"paySplit", type number}, {"modelMonth", type text}, {"payMonthNumber", Int64.Type}})
in
    chgAllTypes

     

    This gives me:

     

    Pete

8 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Hi AndrejZitnay ,

     

    A few things:

     

    - Are you able to share your intake/model data in a PBI-usable format at all? I'm happy to help, but not happy to type in hundreds of values from a screenshot 🙂

    - What do the months in column F represent?

    - What do the percentages in column G represent?

     

    Thanks,

     

    Pete

    • AndrejZitnay's avatar
      AndrejZitnay
      Post Patron
      5 years ago

      Hello BA_Pete ,

       

      You are right.  Here is the link for excel https://uloz.to/tamhle/UziB040kkl20 

      I don't know have PBI but it is very basic as data input in in Intake tab.

      (In real scenario I have 5 yers and 5 campaigns)

       

      I have added there Model A and Model B

      (In real scenario I have 3 models but you get the idea)

       

                   F (For example in Jul-22 start sale of 985 apples) 

                    (It takes 1-12 months to sell it out) 

       

                G (this is spead of conversion (sale)

       

      In model A 2% of sale happens in Month 1, 3% of sale happens in Mth2 etc.

       

      Once apple is sold I takes 1-18 months to collect the money 🙂

       

      Speed of collected money dependes on model.

       

      You can see that peak for Apples on sale from Jul-22 is Mth 8  (Feb-23) in model A but Mth 6 (Dec-22) in model B

       

      Basically in this case model B have better conversion rate.

       

      Many thanks in advance for your help.

      • BA_Pete's avatar
        BA_Pete
        Super User
        5 years ago

        Hi AndrejZitnay ,

         

        Here are the queries from your sample file you provided. The Intake query has all the transformations in it to get to the correct monthly payments based on the modelA criteria.

        You will need to amend the source lines to point to the sample file you sent me.

        I've left all the columns in the Intake query to make it easier for you to follow the steps or extract columns earlier in the process for reporting.

        The modelA and modelAweight queries will also need to be brought in and called these names as they are referenced in the Intake query for a merge and crossjoin respectively.

         

        Intake

        let
    Source = yourSampleFile.xlsx,
    #"Intake _Sheet" = Source{[Item="Intake ",Kind="Sheet"]}[Data],
    promHeads = Table.PromoteHeaders(#"Intake _Sheet", [PromoteAllScalars=true]),
    addModelAweight = Table.AddColumn(promHeads, "modelAweight", each modelAweight),
    expandModelAweight = Table.ExpandTableColumn(addModelAweight, "modelAweight", {"weightMonth", "Weight", "weightMonthNumber"}, {"weightMonth", "Weight", "weightMonthNumber"}),
    addIntakeMonth = Table.AddColumn(expandModelAweight, "intakeMonth", each Date.AddMonths([Month], [weightMonthNumber])),
    addIntakeValue = Table.AddColumn(addIntakeMonth, "intakeValue", each [Intake] * [Weight]),
    mergeModelA = Table.NestedJoin(addIntakeValue, {"weightMonth"}, modelA, {"modelMonth"}, "modelA", JoinKind.LeftOuter),
    expandModelA = Table.ExpandTableColumn(mergeModelA, "modelA", {"payMonthNumber", "paySplit"}, {"payMonthNumber", "paySplit"}),
    addPayMonth = Table.AddColumn(expandModelA, "payMonth", each Date.AddMonths([intakeMonth], [payMonthNumber])),
    addPayValue = Table.AddColumn(addPayMonth, "payValue", each [intakeValue] * [paySplit]),
    chgAllTypes = Table.TransformColumnTypes(addPayValue,{{"Month", type date}, {"Intake", Int64.Type}, {"weightMonth", type text}, {"Weight", type number}, {"intakeMonth", type date}, {"intakeValue", type number}, {"weightMonthNumber", Int64.Type}, {"payMonth", type date}, {"payValue", type number}})
in
    chgAllTypes

         

        modelAweight

        let
    Source = yourSampleFile.xlsx,
    #"Model A_Sheet" = Source{[Item="Model A",Kind="Sheet"]}[Data],
    promHeads = Table.PromoteHeaders(#"Model A_Sheet", [PromoteAllScalars=true]),
    remOthCols = Table.SelectColumns(promHeads,{"Month", "Weight"}),
    renCols = Table.RenameColumns(remOthCols,{{"Month", "weightMonth"}}),
    addMonthNumber = Table.AddColumn(renCols, "weightMonthNumber", each Text.AfterDelimiter([weightMonth], " "), type text),
    chgAllTypes = Table.TransformColumnTypes(addMonthNumber,{{"weightMonth", type text}, {"Weight", type number}, {"weightMonthNumber", Int64.Type}})
in
    chgAllTypes

         

        modelA

        let
    Source = yourSampleFile.xlsx,
    #"Model A_Sheet" = Source{[Item="Model A",Kind="Sheet"]}[Data],
    promHeads = Table.PromoteHeaders(#"Model A_Sheet", [PromoteAllScalars=true]),
    remWeight = Table.RemoveColumns(promHeads,{"Weight"}),
    unpivotOtherCols = Table.UnpivotOtherColumns(remWeight, {"Month"}, "Attribute", "Value"),
    renCols = Table.RenameColumns(unpivotOtherCols,{{"Attribute", "payMonth"}, {"Value", "paySplit"}, {"Month", "modelMonth"}}),
    addModelMonthNumber = Table.AddColumn(renCols, "payMonthNumber", each Text.AfterDelimiter([payMonth], " "), type text),
    chgAllTypes = Table.TransformColumnTypes(addModelMonthNumber,{{"paySplit", type number}, {"modelMonth", type text}, {"payMonthNumber", Int64.Type}})
in
    chgAllTypes

         

        Summary:

        - Crossjoin modelAweight onto Intake table and multiply each original value by weight value.

        - Merge unpivoted modelA table onto Intake table on weightMonth>modelMonth fields, then multiply weighted values by payment split values.

        - Use month number added to start month on each step to give correct future dates for intake and payment.

         

        Hopefully you will be able to see the steps I've taken on each of the queries to get from your sample file data to the final monthly payment schedule but, if not, let me know and I'll try and help.

         

        Pete