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