Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 |
Solved! Go to 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 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)
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
19 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
22 | |
22 |