Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ohurtado
Frequent Visitor

Calculating past averages and creating a new Table

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

SeriesIDPeriodValue
WPU11Jan-20144.6
WPU11Feb-20144.7
WPU11Mar-20144.9
WPU11Apr-20144.8
WPU11May-20144.8
WPU11Jun-20144.8
WPU11Jul-20144.8
WPU11Aug-20145.0
WPU11Sep-20145.0
WPU11Oct-20145.0
WPU11Nov-20145.1
WPU11Dec-20145.2
WPU11Jan-21145.7
WPU11Feb-21146.0
WPU11Mar-21146.4
WPU11Apr-21147.3
WPU11May-21148.1
WPU11Jun-21149.0
WPU11Jul-21150.1
WPU11Aug-21150.9
WPU11Sep-21152.2
WPU11Oct-21153.5
WPU11Nov-21154.5
WPU11Dec-21155.7
WPU11Jan-22158.1
WPU11Feb-22159.5
WPU11Mar-22160.6
WPU11Apr-22162.7
WPU11May-22164.1
WPU11Jun-22165.4
WPU11Jul-22166.4
WPU11Aug-22167.2
WPU11Sep-22167.8
WPU11Oct-22168.7
WPU11Nov-22169.3
WPU11Dec-22169.7
WPU11Jan-23171.9
WPU11Feb-23172.3
WPU11Mar-23173.0
WPU11Apr-23173.7
WPU11May-23173.8
WPU11Jun-23174.0
WPU11Jul-23174.5
WPU11Aug-23174.9
WPU11Sep-23175.0
WPU11Oct-23175.3
WPU10Jan-20219.8
WPU10Feb-20218.5
WPU10Mar-20219.2
WPU10Apr-20215.9
WPU10May-20214.3
WPU10Jun-20215.3
WPU10Jul-20216.3
WPU10Aug-20218.7
WPU10Sep-20221.4
WPU10Oct-20222.2
WPU10Nov-20224.4
WPU10Dec-20232.8
WPU10Jan-21241.5
WPU10Feb-21246.6
WPU10Mar-21260.0
WPU10Apr-21273.4
WPU10May-21283.8
WPU10Jun-21294.5
WPU10Jul-21305.7
WPU10Aug-21313.0
WPU10Sep-21318.3
WPU10Oct-21324.5
WPU10Nov-21332.1
WPU10Dec-21331.9
WPU10Jan-22334.4
WPU10Feb-22330.0
WPU10Mar-22336.0
WPU10Apr-22341.7
WPU10May-22345.2
WPU10Jun-22340.0
WPU10Jul-22328.9
WPU10Aug-22324.3
WPU10Sep-22316.2
WPU10Oct-22308.8
WPU10Nov-22308.3
WPU10Dec-22307.1
WPU10Jan-23310.0
WPU10Feb-23311.8
WPU10Mar-23314.6
WPU10Apr-23316.9
WPU10May-23318.4
WPU10Jun-23315.6
WPU10Jul-23310.9
WPU10Aug-23309.0
WPU10Sep-23306.3
WPU10Oct-23303.9

Intermediate step to calculate averages:

Indexlast 12 months averagelast 24 months averagelast 36 months average
WPU11172.57167.8377161.3201
WPU10311.06320.9218306.1306

 

Expected output:

SeriesIDPeriodValue
WPU11Nov-23172.6
WPU11Dec-23172.6
WPU11Jan-24172.6
WPU11Feb-24172.6
WPU11Mar-24172.6
WPU11Apr-24172.6
WPU11May-24172.6
WPU11Jun-24172.6
WPU11Jul-24172.6
WPU11Aug-24172.6
WPU11Sep-24172.6
WPU11Oct-24172.6
WPU11Nov-24167.8
WPU11Dec-24167.8
WPU11Jan-25167.8
WPU11Feb-25167.8
WPU11Mar-25167.8
WPU11Apr-25167.8
WPU11May-25167.8
WPU11Jun-25167.8
WPU11Jul-25167.8
WPU11Aug-25167.8
WPU11Sep-25167.8
WPU11Oct-25167.8
WPU11Nov-25161.3
WPU11Dec-25161.3
WPU11Jan-26161.3
WPU11Feb-26161.3
WPU11Mar-26161.3
WPU11Apr-26161.3
WPU11May-26161.3
WPU11Jun-26161.3
WPU11Jul-26161.3
WPU11Aug-26161.3
WPU11Sep-26161.3
WPU11Oct-26161.3
WPU10Nov-23311.1
WPU10Dec-23311.1
WPU10Jan-24311.1
WPU10Feb-24311.1
WPU10Mar-24311.1
WPU10Apr-24311.1
WPU10May-24311.1
WPU10Jun-24311.1
WPU10Jul-24311.1
WPU10Aug-24311.1
WPU10Sep-24311.1
WPU10Oct-24311.1
WPU10Nov-24320.9
WPU10Dec-24320.9
WPU10Jan-25320.9
WPU10Feb-25320.9
WPU10Mar-25320.9
WPU10Apr-25320.9
WPU10May-25320.9
WPU10Jun-25320.9
WPU10Jul-25320.9
WPU10Aug-25320.9
WPU10Sep-25320.9
WPU10Oct-25320.9
WPU10Nov-25306.1
WPU10Dec-25306.1
WPU10Jan-26306.1
WPU10Feb-26306.1
WPU10Mar-26306.1
WPU10Apr-26306.1
WPU10May-26306.1
WPU10Jun-26306.1
WPU10Jul-26306.1
WPU10Aug-26306.1
WPU10Sep-26306.1
WPU10Oct-26306.1
1 ACCEPTED SOLUTION
ohurtado
Frequent Visitor

@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)

View solution in original post

3 REPLIES 3
ohurtado
Frequent Visitor

@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
Super User
Super User

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.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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

ohurtado_0-1702456876866.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors