Power BI filter limitng the data range

Hello Anonymous,

 

Can you please try this approach:

3MonthRollingAverage = 
VAR CurrentDate = MAX('DateTable'[Date])
RETURN
    CALCULATE(
        AVERAGE('YourTable'[Price]),
        DATESINPERIOD('DateTable'[Date], CurrentDate, -3, MONTH)
    )

Hi Anonymous 

You need to have a separate dates table that's been marked as a dates table. You can use DATESINPERIOD to get the total for the past x periods. 

3 Months Rolling Amount = 
CALCULATE (
    [Total Revenue],
    DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -3, MONTH )
)

3 Months Rolling Average = 
CALCULATE (
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( Dates, Dates[Month and Year] ),
            "@rev", [Total Revenue]
        ),
        [@rev]
    ),
    DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -3, MONTH )
)

 

Please see attached sample pbix for details.

Hi Anonymous 

 

Thanks for the reply from Sahir_Maharaj and danextian , please allow me to provide another insight:

 

Galactichub , the following testing is for your reference:

 

Sample:

 

1. Create a calculated table as the slicer

Slicer = VALUES('Table'[Month])

 

2. Create a measure as follows

Measure = 
VAR _selected = SELECTEDVALUE('Slicer'[Month])
var _lastMonth = EOMONTH(_selected, -2) + 1
VAR _nextMonth = EOMONTH(_selected, 1)
VAR _average = AVERAGEX(FILTER('Table', [Month] >= _lastMonth && [Month] <= _nextMonth), [average])
RETURN
_average

 

Output:

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.