Quarterly Average from daily forecast data

[LillaF]

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

[Anonymous · Accepted solution]

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.

[LillaF]

Thanks a lot Jayleny, it worked! 😀

[LillaF]

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

[Rupak_bi]

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.

[LillaF]

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

[SachinNandanwar]

Can you post some sample data ?