Forum Discussion

anushaghi123's avatar
anushaghi123
Helper II
2 years ago

Rolling Average

Hi all,  Need help with the following question 

Each product has data, for that we have to create data as a moving average of the last 4 months

For example: United States 37101(product_name) April, May, June, July are 1683,1668,776,1885. Predict Aug as Average of the 4, then use average of May, June July, Aug as September.  And a slider that can be used to do -20% to +20%.  Where we cn check forecast  in case we go -1% of the moving average or 5% of the moving average. I have created a slider using New Parameter 

 

Dax Formula (Quick Measure) - Product_Count rolling average =
IF(
ISFILTERED('Table_name'[dimdate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Table_name'[dimdate].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Table_name'),
'Table_name'[dimdate].[Year],
'Table_name'[dimdate].[QuarterNo],
'Table_name'[dimdate].[Quarter],
'Table_name'[dimdate].[MonthNo],
'Table_name'[dimdate].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Table_name'[Product_Count]),
ALL('Table_name'[dimdate].[Day])
)
)
)

Could you please help.

Thanks

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    anushaghi123 , For rolling 4 month Avg you can use.

     

    example

     

    4 Month Avg = Maxx(Values('Date'[MONTH Year]), CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
    ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH)) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-4,MONTH))

     

    Now you can use Dynamic Segmentation for bucketing of filtering

     

    Dynamic Segmentation Bucketing Binning
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626


    Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

    • anushaghi123's avatar
      anushaghi123
      Helper II
      2 years ago

      HI amitchandak  Thanks for the reply.

       

      I am getting column total error 

       

      I'm getting column total incorrect for Forecast(Blue) data.

      Dax Formula (Quick Measure) - Product_Count rolling average =
      IF(
      ISFILTERED('Table_name'[dimdate]),
      ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
      VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])
      VAR __DATE_PERIOD =
      DATESBETWEEN(
      'Table_name'[dimdate].[Date],
      STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),
      __LAST_DATE
      )
      RETURN
      AVERAGEX(
      CALCULATETABLE(
      SUMMARIZE(
      VALUES('Table_name'),
      'Table_name'[dimdate].[Year],
      'Table_name'[dimdate].[QuarterNo],
      'Table_name'[dimdate].[Quarter],
      'Table_name'[dimdate].[MonthNo],
      'Table_name'[dimdate].[Month]
      ),
      __DATE_PERIOD
      ),
      CALCULATE(
      SUM('Table_name'[Product_Count]),
      ALL('Table_name'[dimdate].[Day])
      )
      )
      )

       

      Could you check the DAX

      Thanks