DAX - Calculate forecast in cascade depending by previous month also forecasted in same column table

[cosminc]

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

[v-juanli-msft]

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.

[cosminc]

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

[v-juanli-msft]

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.

[cosminc]

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