topic Re: Quarterly Average from daily forecast data in DAX Commands and Tips

Quarterly Average from daily forecast data

LillaF — Fri, 22 Nov 2024 17:04:26 GMT

Guys,

I need help in calculating the quarterly average forecast from my forecast values (need a DAX measure).

I have a fact table, 'Forecasted Value':

And a Calendar table:

The two are linked by the "Date" column in the Data model.

Can you help how I should go about this?

Thanks a lot!

Lilla

Re: Quarterly Average from daily forecast data

Rupak_bi — Sat, 23 Nov 2024 07:21:35 GMT

Hi LillaF ,

What is your desired output? See if you create a matrix using the fact table, keeping date (hirerchey) in the row , average of forcast in the value and family in the column, you should get quarterly avg values. hope you have already tried this. SO were are you stucking.

Re: Quarterly Average from daily forecast data

LillaF — Sat, 23 Nov 2024 13:03:46 GMT

Hi Rupak_bi,

Thanks for answering. Actually my customer wants to compare the 3-months rolling forecast with the quarterly average forecast value, that's why I would rather keep the monthly scales in the rows and not go for date hierarchy.

I have created the 3-months rolling forecast measure, see in Report view:

3-months Rolling Forecast =

CALCULATE([Total Forecast],DATESINPERIOD(Calendar_Lilla[Date],MAX(Calendar_Lilla[Date]),-3,MONTH))

And I would need to add the quarterly average forecast to this matrix. So I'm not looking for the monthly forecast like this:

But I need the qly avg forecast in this column, while keeping the monthly scale in the matrix. (So for Oct'24, Nov'24 and Dec'24, I would need avg $660,767 to be displayed. For Jan'25, Feb'25, Mar'25, I would need $1,784,158 to be displayed.

Wondering if it's feasible somehow..?

If not, I will go for changing the dates for dates (hierarchy).

Thank you!

Lilla

Re: Quarterly Average from daily forecast data

SachinNandanwar — Sat, 23 Nov 2024 18:23:19 GMT

Can you post some sample data ?

Re: Quarterly Average from daily forecast data

LillaF — Mon, 25 Nov 2024 15:05:36 GMT

Hi Sachin,

Sure, please see here: https://docs.google.com/spreadsheets/d/1Gn-cXFcAoEMto5wqLRj2Tp62gu9JEuaEGXJdVvDJrbY/edit?usp=sharing

This is the desired output. I'm not sure how to make this look kinda the same in Power BI.

Thanks:

Lilla

Re: Quarterly Average from daily forecast data

Anonymous — Tue, 26 Nov 2024 05:56:31 GMT

Hi LillaF

Please try the following measure:

Measure = VAR _quarter = QUARTER(SELECTEDVALUE('Table'[Date])) VAR _year = YEAR(SELECTEDVALUE('Table'[Date])) RETURN CALCULATE(AVERAGEX(FILTER(ALL('Table'),QUARTER('Table'[Date]) = _quarter && YEAR('Table'[Date]) = _year),'Table'[Total Forecast]))

Result:

Best Regards,

Jayleny

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

Re: Quarterly Average from daily forecast data

LillaF — Fri, 29 Nov 2024 08:05:36 GMT

Thanks a lot Jayleny, it worked! 😀