Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |