Forum Discussion

LillaF's avatar
LillaF
New Member
1 year ago
Solved

Quarterly Average from daily forecast data

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's avatar
    Anonymous
    1 year ago

    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.

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      LillaF
      New Member
      1 year ago

      Thanks a lot Jayleny, it worked! 😀

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      LillaF
      New Member
      1 year ago

      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