Calculate future month values using the previous month value

[shkabuzar]

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

[Anonymous · Accepted solution]

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.

[Syndicate_Admin]

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?

[shkabuzar]

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.

[lbendlin]

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.

[shkabuzar]

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