Forum Discussion
Calculate future month values using the previous month value
Hi,
My data looks as below and future values to be calculated using previous month values.
| Table1 | |
| year_month | 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 |
| Calendar table |
| Date |
| 01-10-2020 |
| 01-11-2020 |
| 01-12-2020 |
| 01-01-2021 |
| 01-02-2021 |
| 01-03-2021 |
| 01-04-2021 |
| 01-05-2021 |
| 01-06-2021 |
| 01-07-2021 |
| 01-08-2021 |
| 01-09-2021 |
| 01-10-2021 |
| 01-11-2021 |
| 01-12-2021 |
| 01-01-2022 |
| 01-02-2022 |
| 01-03-2022 |
| 01-04-2022 |
| 01-05-2022 |
| 01-06-2022 |
| Expected data | |||||
| 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 | expected value- | previous month value*(1+0.1) | 15*(1+0.1) | 16.5 | |
| 01-09-2021 | expected value- | (previous month value=16.5)*(1+0.1) | 16.5*(1+0.1) | 18.15 | |
| 01-10-2021 | expected value- | (previous month value=18.15)*(1+0.1) | 18.15*(1+0.1) | 19.96 | |
| 01-11-2021 | continues | ||||
| 01-12-2021 | |||||
| 01-01-2022 | |||||
| 01-02-2022 | |||||
| 01-03-2022 | |||||
| 01-04-2022 | |||||
| 01-05-2022 | |||||
| 01-06-2022 |
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.
5 Replies
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.Thank you, The above measure is working for me. I have to calculate running total on the measure too, means I would like to have another measure/column with running total. The Final data should be as below. How to calculate the running total, I have tried the quick measure, but the it not giving the accurate numbers.
I'm interested in your solution, but I have a problem. I have my Calendar table linked to my fact table. How can I d
isplay all the future data?
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
