Forum Discussion
Future dates with estimated values
Hello
I am trying to set up a table with percentage increases by future month, based off of the previous month. For example, x is last completed month, and I want a 10% increase for the next 12 months, so essentially x * 1.1 to the power of the month number. For example, December value is 100, I want to estimate a 10% increase by month for 2022, so January is 100 * 10%, February is January's value * 10%, March is February's value * 10%, etc. But now as January ends and I have actual number for January, all the future months need to now adjust to what the actual value of January was, not the estimate, and so on.
Any help with how to set this up would be greatly appreciated.
Hi aashton ,
I think you want to calculate the forecast price by logic as "actual price" *1.1^x. Here I create a sample to have a test.
My Sample:
Date table:
Date = VAR _TABLE1 = ADDCOLUMNS( CALENDAR(DATE(2021,11,01),DATE(2023,01,31)),"Year",YEAR([Date]),"Month",MONTH([Date]),"YearMonth",YEAR([Date])*100+MONTH([Date])) VAR _ADD = ADDCOLUMNS(_TABLE1,"Rank",RANKX(_TABLE1,[YearMonth],,ASC,Dense)) RETURN _ADDRelationship:
Measure:
Measure = VAR _MAXYEARMONTH = MAX(Actual[YearMonth]) VAR _STARTRANK = CALCULATE(MAX('Date'[Rank]),FILTER(ALL('Date'),'Date'[YearMonth] = _MAXYEARMONTH)) VAR _ACTUAL = CALCULATE(SUM(Actual[Actual]),FILTER(Actual,Actual[YearMonth] = _MAXYEARMONTH)) VAR _SUMMARIZE = SUMMARIZE(FILTER('Date','Date'[Rank]>=_STARTRANK),'Date'[Year],'Date'[Month],'Date'[YearMonth],'Date'[Rank]) VAR _ADD = ADDCOLUMNS(_SUMMARIZE,"Price",IF('Date'[Rank] = _STARTRANK, _ACTUAL,_ACTUAL* POWER(1.1,'Date'[Rank]-_STARTRANK))) RETURN SUMX(_ADD,[Price])Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
aashton I have similar quetion answered here, check this post https://community.powerbi.com/t5/Desktop/What-if-Parameter-Month-over-Month-to-predict-sales/m-p/2228053#M812762
also, there is an attachment that you can use and tweak as per your need.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thank you, I'll check it out.
Hi aashton ,
I think you want to calculate the forecast price by logic as "actual price" *1.1^x. Here I create a sample to have a test.
My Sample:
Date table:
Date = VAR _TABLE1 = ADDCOLUMNS( CALENDAR(DATE(2021,11,01),DATE(2023,01,31)),"Year",YEAR([Date]),"Month",MONTH([Date]),"YearMonth",YEAR([Date])*100+MONTH([Date])) VAR _ADD = ADDCOLUMNS(_TABLE1,"Rank",RANKX(_TABLE1,[YearMonth],,ASC,Dense)) RETURN _ADDRelationship:
Measure:
Measure = VAR _MAXYEARMONTH = MAX(Actual[YearMonth]) VAR _STARTRANK = CALCULATE(MAX('Date'[Rank]),FILTER(ALL('Date'),'Date'[YearMonth] = _MAXYEARMONTH)) VAR _ACTUAL = CALCULATE(SUM(Actual[Actual]),FILTER(Actual,Actual[YearMonth] = _MAXYEARMONTH)) VAR _SUMMARIZE = SUMMARIZE(FILTER('Date','Date'[Rank]>=_STARTRANK),'Date'[Year],'Date'[Month],'Date'[YearMonth],'Date'[Rank]) VAR _ADD = ADDCOLUMNS(_SUMMARIZE,"Price",IF('Date'[Rank] = _STARTRANK, _ACTUAL,_ACTUAL* POWER(1.1,'Date'[Rank]-_STARTRANK))) RETURN SUMX(_ADD,[Price])Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
