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

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