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 ,

 

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's avatar
BA_Pete
Super User
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

    Thank you very much BA_Pete 

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

    Much appreciated.