Forum Discussion
Model calculation
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 chgAllTypesThis gives me:
Pete
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
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
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
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 chgAllTypesThis gives me:
Pete