Forum Discussion

aashton's avatar
aashton
Helper V
4 years ago
Solved

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 mon...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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
_ADD

    Relationship:

    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 Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

parry2k's avatar
parry2k
Super User
4 years ago

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

aashton's avatar
aashton
Helper V
4 years ago

Thank you, I'll check it out.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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
_ADD

    Relationship:

    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 Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.