Model calculation

[AndrejZitnay]

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.

[BA_Pete]

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]

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]

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

[AndrejZitnay]

Hello @BA_Pete ,

Thank you very much for your kind help.

I think that we are almost there.

I have uploaded again excel file and as well PBIX test file.

https://ulozto.cz/tamhle/eCKBLTQv1Kfk/name/Nahrano-8-2-2021-v-22-18-00

Let's follow up story with Jul-22 and intake of 985

In my column G Months 1-12 it should start with Jul-22

PayMonth should always start as following month but have to end up on 18th month

In this example from Aug-22 to Jan-24

In my PBIX test file is intake Month starting with Aug-22 and Pay Month from Sep-22 to Jan-25

Would you be so kind and help me with this correction?

Many thanks in advance for your help.

Andrej

[BA_Pete]

@AndrejZitnay 

Try this new Intake query and let me know if ok.

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] - 1)),
    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

Pete 

[AndrejZitnay]

Hello @BA_Pete ,

I've used new intake and months are starting correctly.

Thank you for that.

One last problem is that we don't have only 18 months if i select my model month.

Figures are correct. We end up with same outcome as intake.

Individual figures are correct as well only problem is with where they are.

Here is the issue:

weightMonthNumber 1  (Jul22) is fine.          1st pay out month is in Aug -22

weightMonthNumber  2 (Aug22) is not fine.  1st pay out month is not Sep-22 as per model but Oct-22

weightMonthNumber  3 (Sep22) is not fine.  1st pay out month is not Oct-22 as per model but Dec-22

etc.

Every month pay out month is pushed by another months so 

weightMonthNumber  12 (Jun23) is not fine.  1st pay out month is not July-23 as per model but Jun-24

I think that if this will be corrected then I will end up with only 18 months if i select my model month.

Would you be so kind and look for me into that?

Many thanks.

Andrej

[BA_Pete · Accepted solution]

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]

Thank you very much @BA_Pete 

you are start and have saved me lot's of manual work.

Much appreciated.