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 all,
is there a way to calculate forecasted values in cascade?
let's say we have a table like this:
Year-Month Value
2019-10 5
2019-11 7
2019-12 8
and i want to obtain a table like this:
Date Value
2019-10 5
2019-11 7
2019-12 8
2020-01 2019-12 *evolution index between 2019-12 and 2019-11
2020-02 2020-01 *evolution index
2020-03 2020-02 * evolution index
so the idea is how to create a dax where you can use also forecasted data from previous month in same column, in cascade?
a measure or a column in dax
Thanks,
Cosmin
Hi @cosminc
This is possible.
Please tell me the forecasting rule.
We can create a measure like:
measure=if(max(date[date])<=[specific date], [actual value],[forecast value])
I need more information to accomplish the measure.
Besides, you can refer to a simple way to forecast via visuals.
https://www.youtube.com/watch?v=XIlPkyyztho
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks,
forecast measure i need to calculate it, not bi.
as measure, i have a given formula; you can built any chain in cascade.
also i need to calculate month by month but to vizulize by quarter.
follow your measure
measure=if(max(date[date])<=[specific date], [actual value],[forecast value])
where actual data are till November 2019 and i need to create a rule in measure or in column to compute values on 3rd column in below visual
i want VAR forecast_value = value from previous month + 1
please have in mind that forecast_value is more complex in real base, but it's based on value from previous month from a column and some coefficients and other value from other column but from previous month.
in my measure third column stops on dec 2019 where i use value from last actual month; i need to obtain a chain of values till dec 2020 in cascade, based on previous month, so from jan 2020 i need to use calculated value from dec 2019.
Thanks a lot.
Cosmin
Hi @cosminc
Create a measure
forecast =
VAR lastdate1 =
CALCULATE (
LASTDATE ( 'date'[Date] ),
FILTER (
ALLSELECTED ( 'Table 3' ),
'Table 3'[actual]
<> BLANK ()
)
)
VAR previousmonthvalue =
CALCULATE (
SUM ( 'Table 3'[actual] ),
FILTER (
ALLSELECTED ( 'date' ),
DATEDIFF (
'date'[Date],
MAX ( 'date'[Date] ),
MONTH
) = 1
)
)
RETURN
IF (
MAX ( 'date'[Date] )
> EOMONTH (
lastdate1,
0
),
previousmonthvalue + 1,
SUM ( 'Table 3'[actual] )
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
i see in your pbix you have same results as me =1 for future months; doesn't follow the cascade.
sep 2020 or dec 2020 - value is 1
i need to calculate in cascade dec 2020 in connection with nov 2020...nov 2020 depending oct 2020 and so on.
do i miss something?
i need like this
Year | Month | actual | forecast |
2019 | January | 25.47 | 25.47 |
2019 | February | 25.61 | 25.61 |
2019 | March | 25.44 | 25.44 |
2019 | April | 25.56 | 25.56 |
2019 | May | 25.52 | 25.52 |
2019 | June | 25.65 | 25.65 |
2019 | July | 24.97 | 24.97 |
2019 | August | 25.31 | 25.31 |
2019 | September | 26.07 | 26.07 |
2019 | October | 24.96 | 24.96 |
2019 | November | 25.24 | 25.24 |
2019 | December | 26.24 | |
2020 | January | 27.24 | |
2020 | February | 28.24 | |
2020 | March | 29.24 | |
2020 | April | 30.24 | |
2020 | May | 31.24 | |
2020 | June | 32.24 | |
2020 | July | 33.24 | |
2020 | August | 34.24 | |
2020 | September | 35.24 | |
2020 | October | 36.24 | |
2020 | November | 37.24 | |
2020 | December | 38.24 |
Thanks,
Cosmin
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |