Forum Discussion
Calculate future month values using the previous month value
Hi shkabuzar ,
Please try the following formula to create a measure:
Measure = VAR _date = CALCULATE ( MAX ( 'Table1'[year_month] ), FILTER ( 'Table1', 'Table1'[year_month] <= MAX ( 'Calendar table'[Date] ) ) ) VAR _value = CALCULATE ( MAX ( 'Table1'[Value] ), 'Table1'[year_month] = _date ) VAR _monthdiff = DATEDIFF ( _date, MAX ( 'Calendar table'[Date] ), MONTH ) RETURN IF ( _monthdiff = 0, _value, _value * POWER ( 1.1, _monthdiff ) )Here is the final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super UserWhat have you tried? In Power Query you would do that via List.Accumulate() , and in DAX via PRODUCTX(). Should it be a measure or a fixed value?
You may also want to consider a sliding window (for example last 12 months performance). A 10% increase month over month is not supported by your facts.
HI I have tried the below messure:
forecast=
VAR MAx_date=CALCULATE(LASTDATE(table1[year_month]),REMOVEFILTERS())
VAR last_value =
CALCULATE(
SUMx(table1,table1[Value]),
DATEADD('calendar table'[date],-1,MONTH)
)*(1+0.1)
Var result=
If(SELECTEDVALUE('calendar'[date])>[MAx_date],[value],last_value,sum(value))
return
result
Using the mesure I am geeting the value for one month,not geeting the values for entire date range.Current o/p using the above measure Date Value 01-10-2020 10 01-11-2020 15 01-12-2020 22 01-01-2021 34 01-02-2021 12 01-03-2021 23 01-04-2021 14 01-05-2021 25 01-06-2021 24 01-07-2021 15 01-08-2021 16.5 01-09-2021 01-10-2021 01-11-2021 01-12-2021 01-01-2022
