Forum Discussion
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...
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.
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 Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.