Question

I need to show a trendline for a moving average but only for selected date ranges—how can I do this?
I need to create a moving average trendline in Power BI that adjusts dynamically based on a user-selected date range. The trendline should update when users filter specific periods while maintaining accuracy in calculations. What is the best approach using DAX and time intelligence functions to ensure the moving average reflects only the selected dates?

Answer 1

To do a Moving Average Trendline that will keep on updating dynamically according to the user-selected date range in Power BI, do the following:

1. Create a Moving Average Measure

DAX is used to calculate moving averages based on the specified time frame, such as 7-day or 30-day averages.

MovingAvg =  
VAR DaysBack = 7  
RETURN  
AVERAGEX(  
    DATESINPERIOD(  
        'Date'[Date],  
        MAX('Date'[Date]),  
        -DaysBack, DAY  
    ),  
    CALCULATE(SUM('Sales'[SalesAmount]))  
)

Modify DaysBack as per the required period, e.g., -30 for a 30-day moving average.

2. Enable Dynamic Date Filtering

Whenever the users filter a specific date range as per their requirement through the slicers, Power BI manages to dynamically filter the MAX('Date'[Date]) in the DAX formula. Therefore, this makes sure that the moving average applies only to the range selected.

3. Charting the Trendline

Use a line chart with Date along the X-axis and MovingAvg against the Y-axis and overlay the same with actuals for comparison.