Forum Discussion

anushaghi123's avatar
anushaghi123
Helper II
3 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,...
Need Help
amitchandak's avatar
amitchandak
Super User
3 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