Forecast Versions and lag calculations

[LucasD]

Every month, we generate a forecast for the next 6 months.

When the month ends, the version of the forecast is stored as the version of that month.

I would like to create a measure that returns what was forecasted for that month in the previous version.

The table looks like this:

Date Month	Forecast Version	Forecast in KGs
202403	202403	8
202404	202403	5 (x)
202405	202403	4
202406	202403	6
202407	202403	9
202408	202403	10
202404	202404	11
202405	202404	7 (y)
202406	202404	6
202407	202404	7
202408	202404	8
202409	202404	9
202405	202405	6
202406	202405	3 (z)
202407	202405	8
202408	202405	1
202409	202405	3
202410	202405	2

The measure should return:

Date Month	Forecast Lag -1
202403
202404	5 (x)
202405	7 (y)
202406	3 (z)
202407
202408
202409
202410

If easier for the measure, you could use Date Month and Forecast Version as if they were a Date (date can be Start or End of Month)

Thanks in advance!

[LucasD · Accepted solution]

Hey,

Found the solution. I did the following:

Created a calculated column called "Lag"

Date Month	Forecast Version	Forecast in KGs	Lag
01/03/2024	01/03/2024	8	0
01/04/2024	01/03/2024	5	1
01/05/2024	01/03/2024	4	2
01/06/2024	01/03/2024	6	3
01/07/2024	01/03/2024	9	4
01/08/2024	01/03/2024	10	5
01/04/2024	01/04/2024	11	0
01/05/2024	01/04/2024	7	1
01/06/2024	01/04/2024	6	2
01/07/2024	01/04/2024	7	3
01/08/2024	01/04/2024	8	4
01/09/2024	01/04/2024	9	5
01/05/2024	01/05/2024	6	0
01/06/2024	01/05/2024	3	1
01/07/2024	01/05/2024	8	2
01/08/2024	01/05/2024	1	3
01/09/2024	01/05/2024	3	4
01/10/2024	01/05/2024	2	5

Where Lag is =DATEDIFF(Table1[Forecast Version],Table1[Date Month],MONTH)

The measure then looks like this:

Forecast Lag - 1:=CALCULATE(SUM([Forecast in KGs]),Table1[LAG]=1)

Results:

Date Month	Forecast Lag - 1
01/04/2024	5
01/05/2024	7
01/06/2024	3

[Anonymous]

Hi @LucasD ,

Try formula like below:

Forecast Lag -1 = 
VAR CurrentMonth = MAX('Table'[EndOfMonth])
VAR CurrentVersion = MAX('Table'[Forecast Version])
VAR PreviousVersion = CurrentVersion -1
var Len_ = LEN(MAX('Table'[Forecast in KGs]))
RETURN 
    CALCULATE(
        MAX('Table'[Forecast in KGs]),
        FILTER(
            'Table',
            Len_> 2 
            && 'Table'[Forecast Version] = PreviousVersion
        )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gmsamborn]

Hi @LucasD 

Would these measures help?

Forecast = SUM( 'Table'[Forecast in KGs] )

Same Month - Previous Version = 
    CALCULATE(
        [Forecast],
        OFFSET (
            -1,
            ALLSELECTED ( 'Table'[Forecast Version] ),
            ORDERBY ( 'Table'[Forecast Version], ASC )
        )
    )

Lag = [Forecast] - [Same Month - Previous Version]

I hope I understood your requirement.

Same Month - Previous Version.pbix

[LucasD]

Hi, thanks for your reply.

That did not work, as the offset is including all planning versions but the last. So, it is including the values of two planning versions at the same time. What I need is to include only one, the next planning version.

I made it work by creating two calendar tables (one for the forecast version and one for the date month) and included a rank for the months.

Then the formula I used, which can't explain how it works (but works) is this one:

FCT T-1 =

VAR VersionMonth = DATEADD(CalendarTablePlanningVersion[DateId],0,MONTH)

VAR ForecastMonth = DATEADD(CalendarTableForecastDateMonth[DatesId],0,MONTH)

RETURN

CALCULATE([Forecast],VersionMonth,ForecastMonth,CalendarTableForecastDateMonth[Ranking]=CalendarTablePlanningVersion[Ranking]+1)