Calculating past averages and creating a new Table

[ohurtado]

Hi, relatively new to DAX and facing some issues as I'm trying to build the following data.

I have historical value for certain indexes (say data published by Bureau of Labour and Statistics) -> Mainly Series ID, date, value

I would like to build a future forecast of these values based on past data -> i.e. I would need the last 12 month average, last 24 months average and last 36 months average -> this needs to be done on a per seriesID basis.

I would like then to show graphically the past data and the the forecast values (the N+12 months should be last 12 month average, month N+12 to N+24 the last 24 month average, and month N+24 to N+36 the last 36 months average). For this part I believe a new table is needed creating dynamically values for the next 36 months for each SeriesID right?

How would you go about this? 

For reference, some sample data and expected output.

Original Data

SeriesID	Period	Value
WPU11	Jan-20	144.6
WPU11	Feb-20	144.7
WPU11	Mar-20	144.9
WPU11	Apr-20	144.8
WPU11	May-20	144.8
WPU11	Jun-20	144.8
WPU11	Jul-20	144.8
WPU11	Aug-20	145.0
WPU11	Sep-20	145.0
WPU11	Oct-20	145.0
WPU11	Nov-20	145.1
WPU11	Dec-20	145.2
WPU11	Jan-21	145.7
WPU11	Feb-21	146.0
WPU11	Mar-21	146.4
WPU11	Apr-21	147.3
WPU11	May-21	148.1
WPU11	Jun-21	149.0
WPU11	Jul-21	150.1
WPU11	Aug-21	150.9
WPU11	Sep-21	152.2
WPU11	Oct-21	153.5
WPU11	Nov-21	154.5
WPU11	Dec-21	155.7
WPU11	Jan-22	158.1
WPU11	Feb-22	159.5
WPU11	Mar-22	160.6
WPU11	Apr-22	162.7
WPU11	May-22	164.1
WPU11	Jun-22	165.4
WPU11	Jul-22	166.4
WPU11	Aug-22	167.2
WPU11	Sep-22	167.8
WPU11	Oct-22	168.7
WPU11	Nov-22	169.3
WPU11	Dec-22	169.7
WPU11	Jan-23	171.9
WPU11	Feb-23	172.3
WPU11	Mar-23	173.0
WPU11	Apr-23	173.7
WPU11	May-23	173.8
WPU11	Jun-23	174.0
WPU11	Jul-23	174.5
WPU11	Aug-23	174.9
WPU11	Sep-23	175.0
WPU11	Oct-23	175.3
WPU10	Jan-20	219.8
WPU10	Feb-20	218.5
WPU10	Mar-20	219.2
WPU10	Apr-20	215.9
WPU10	May-20	214.3
WPU10	Jun-20	215.3
WPU10	Jul-20	216.3
WPU10	Aug-20	218.7
WPU10	Sep-20	221.4
WPU10	Oct-20	222.2
WPU10	Nov-20	224.4
WPU10	Dec-20	232.8
WPU10	Jan-21	241.5
WPU10	Feb-21	246.6
WPU10	Mar-21	260.0
WPU10	Apr-21	273.4
WPU10	May-21	283.8
WPU10	Jun-21	294.5
WPU10	Jul-21	305.7
WPU10	Aug-21	313.0
WPU10	Sep-21	318.3
WPU10	Oct-21	324.5
WPU10	Nov-21	332.1
WPU10	Dec-21	331.9
WPU10	Jan-22	334.4
WPU10	Feb-22	330.0
WPU10	Mar-22	336.0
WPU10	Apr-22	341.7
WPU10	May-22	345.2
WPU10	Jun-22	340.0
WPU10	Jul-22	328.9
WPU10	Aug-22	324.3
WPU10	Sep-22	316.2
WPU10	Oct-22	308.8
WPU10	Nov-22	308.3
WPU10	Dec-22	307.1
WPU10	Jan-23	310.0
WPU10	Feb-23	311.8
WPU10	Mar-23	314.6
WPU10	Apr-23	316.9
WPU10	May-23	318.4
WPU10	Jun-23	315.6
WPU10	Jul-23	310.9
WPU10	Aug-23	309.0
WPU10	Sep-23	306.3
WPU10	Oct-23	303.9

Intermediate step to calculate averages:

Index	last 12 months average	last 24 months average	last 36 months average
WPU11	172.57	167.8377	161.3201
WPU10	311.06	320.9218	306.1306

Expected output:

SeriesID	Period	Value
WPU11	Nov-23	172.6
WPU11	Dec-23	172.6
WPU11	Jan-24	172.6
WPU11	Feb-24	172.6
WPU11	Mar-24	172.6
WPU11	Apr-24	172.6
WPU11	May-24	172.6
WPU11	Jun-24	172.6
WPU11	Jul-24	172.6
WPU11	Aug-24	172.6
WPU11	Sep-24	172.6
WPU11	Oct-24	172.6
WPU11	Nov-24	167.8
WPU11	Dec-24	167.8
WPU11	Jan-25	167.8
WPU11	Feb-25	167.8
WPU11	Mar-25	167.8
WPU11	Apr-25	167.8
WPU11	May-25	167.8
WPU11	Jun-25	167.8
WPU11	Jul-25	167.8
WPU11	Aug-25	167.8
WPU11	Sep-25	167.8
WPU11	Oct-25	167.8
WPU11	Nov-25	161.3
WPU11	Dec-25	161.3
WPU11	Jan-26	161.3
WPU11	Feb-26	161.3
WPU11	Mar-26	161.3
WPU11	Apr-26	161.3
WPU11	May-26	161.3
WPU11	Jun-26	161.3
WPU11	Jul-26	161.3
WPU11	Aug-26	161.3
WPU11	Sep-26	161.3
WPU11	Oct-26	161.3
WPU10	Nov-23	311.1
WPU10	Dec-23	311.1
WPU10	Jan-24	311.1
WPU10	Feb-24	311.1
WPU10	Mar-24	311.1
WPU10	Apr-24	311.1
WPU10	May-24	311.1
WPU10	Jun-24	311.1
WPU10	Jul-24	311.1
WPU10	Aug-24	311.1
WPU10	Sep-24	311.1
WPU10	Oct-24	311.1
WPU10	Nov-24	320.9
WPU10	Dec-24	320.9
WPU10	Jan-25	320.9
WPU10	Feb-25	320.9
WPU10	Mar-25	320.9
WPU10	Apr-25	320.9
WPU10	May-25	320.9
WPU10	Jun-25	320.9
WPU10	Jul-25	320.9
WPU10	Aug-25	320.9
WPU10	Sep-25	320.9
WPU10	Oct-25	320.9
WPU10	Nov-25	306.1
WPU10	Dec-25	306.1
WPU10	Jan-26	306.1
WPU10	Feb-26	306.1
WPU10	Mar-26	306.1
WPU10	Apr-26	306.1
WPU10	May-26	306.1
WPU10	Jun-26	306.1
WPU10	Jul-26	306.1
WPU10	Aug-26	306.1
WPU10	Sep-26	306.1
WPU10	Oct-26	306.1

[ohurtado · Accepted solution]

@Sahir_Maharaj just fixed a couple of things and I think it is starting to work!
for the last 12 month average I rewrote the measure as such:

AverageLast12Months = 
Var MyAverage = CALCULATE(
    AVERAGE(IndexData[Value]),
    DATESINPERIOD(IndexData[Period],LASTDATE(IndexData[Period]), -12, MONTH)
    )
    RETURN
    MyAverage

Then for the table generation I did as such (simplified to just 12 months for test): 

TestAverages = GENERATE(
    DISTINCT(IndexData[SeriesID]),
    VAR currentSeriesID = IndexData[SeriesID]
    VAR maxDate = CALCULATE(MAX(IndexData[Period]), IndexData[SeriesID] = currentSeriesID)
    VAR minDate = DATE(YEAR(maxDate)-1, MONTH(maxDate),1)
    Var plus12Months = DATE(YEAR(maxDate)+1, MONTH(maxDate),1)
    RETURN
    SELECTCOLUMNS(
        FILTER('FutureDates', 'FutureDates'[Date] > maxDate && 'FutureDates'[Date] <= plus12Months && FutureDates[Date].[Day]=1),
        "SeriesID2", currentSeriesID,
        "Period", 'FutureDates'[Date],
        "Value", 
        SWITCH(
            TRUE(),
            'FutureDates'[Date] <= plus12Months, [AverageLast12Months],
            [AverageLast12Months]
        )
    )
)

 I believe the values are correct, however I need to add the 24 and 36 months. The code was adding values for each day so I added another condition to just keep one value per month (Day = 1)

[Sahir_Maharaj]

Hello @ohurtado,

Can you please try this:

1. Calculate Rolling Averages

RollingAvg12M = 
CALCULATE(
    AVERAGE('YourData'[Value]),
    FILTER(
        'YourData',
        'YourData'[Date] >= DATEADD(LASTDATE('YourData'[Date]), -12, MONTH) && 
        'YourData'[Date] < LASTDATE('YourData'[Date])
    ),
    'YourData'[SeriesID]
)

2. Create a Future Dates Table

FutureDates = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date])
)

3. Create a Forecast Table

ForecastTable = 
GENERATE(
    DISTINCT('YourData'[SeriesID]),
    VAR currentSeriesID = 'YourData'[SeriesID]
    VAR maxDate = CALCULATE(MAX('YourData'[Date]), 'YourData'[SeriesID] = currentSeriesID)
    RETURN
    SELECTCOLUMNS(
        FILTER('FutureDates', 'FutureDates'[Date] > maxDate && 'FutureDates'[Date] <= DATEADD(maxDate, 36, MONTH)),
        "SeriesID", currentSeriesID,
        "Period", 'FutureDates'[Date],
        "Value", 
        SWITCH(
            TRUE(),
            'FutureDates'[Date] <= DATEADD(maxDate, 12, MONTH), [RollingAvg12M],
            'FutureDates'[Date] <= DATEADD(maxDate, 24, MONTH), [RollingAvg24M],
            [RollingAvg36M]
        )
    )
)

Hope this helps! Should you require any further assistance, please do not hesitate to reach out to me.

[ohurtado]

HI @Sahir_Maharaj , thanks a lot for the answer. Still facing some issues with the code, but I see the idea of what you are trying to do and I think it's already helping me a lot!

1st issue: on the 12 month rolling average measure I get an error when testing it in a visual (I expect if I put this on a table with seriesID as rows and measure as value I would be able to get my "intermediate table" to check the averages are correctly calculated...) -> Visual does not work with the following error -> Calculation error in measure: Cannot convert value 'WPU10' of type Text to type True/False.

2nd issue so far -> on the generate table of the forecasted values, I cannot do this line of code: 

FILTER('FutureDates', 'FutureDates'[Date] > maxDate && 'FutureDates'[Date] <= DATEADD(maxDate, 36, MONTH)),

I get the error that first argument to 'DATEADD' must specify a column and we are trying to pass a variable