Forecast - Recurrence logic error

[Anonymous]

Hi!

I want to make a forecast based on some different calculated columns.

My starting point is the number of Closed cases per day. This is a simple logic.

Next, I calculate the Rolling average of closed cases per employee. Dax below:

Closed_cases_on_date_person rolling average = 
VAR __LAST_DATE = LASTDATE('Dates'[Date])
RETURN
    AVERAGEX(
        DATESBETWEEN(
            'Dates'[Date];
            DATEADD(__LAST_DATE; -30; DAY);
            __LAST_DATE
        );
        CALCULATE(AVERAGE(Dates[Closed_Per_Person])
    ))+0

Using this, I forecast the upcoming number of total cases that will be closed: 

Closed_cases_on_date_forecast = 
IF(Dates[Date] <= TODAY();CALCULATE (
    DISTINCTCOUNT ( UserActivity[ID] );
    FILTER (
        UserActivity; UserActivity[To status] in {"Closed"; "Canceled"} &&
        DateValue(UserActivity[EndDate]) =  Dates[Date]
    )
)+0;
Dates[Closed_cases_on_date_person rolling average] * [FTEs_Active])

Now I would like to combine this logic. I would like my rolling average to be based on Closed_cases_on_date_forecast and also the other way around. This way, I want to have a longer forecast that automatically uses previously forecasted numbers.

How can I do this?

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Please have a try 🙂

Closed_cases_on_date_forecast =
VAR __LAST_DATE =
    LASTDATE ( 'Dates'[Date] )
VAR average =
    AVERAGEX (
        DATESBETWEEN ( 'Dates'[Date]; DATEADD ( __LAST_DATE; -30; DAY ); __LAST_DATE );
        CALCULATE ( AVERAGE ( Dates[Closed_Per_Person] ) )
    ) + 0
RETURN
    IF (
        Dates[Date] <= TODAY ();
        CALCULATE (
            DISTINCTCOUNT ( UserActivity[ID] );
            FILTER (
                UserActivity;
                UserActivity[To status] IN { "Closed"; "Canceled" }
                    && DATEVALUE ( UserActivity[EndDate] ) = Dates[Date]
            )
        ) + 0;
        average * [FTEs_Active]
    )