Power BI filter limitng the data range

[Anonymous]

Hello,

I have encountered issues with including 3 months rolling average on power BI

for example, if the selected filter starts from January, it needs to show the 3months average rolling price of December, January and Feburary. However, since the filter starts from January, December data cannot be used to calculate the 3months rolling average... 

let's say if the average price of the december is 10000 and January 20000 and feburary 30000

then the 3month rolling average of January supposed to be 20000. but since the filter limits the starting data, it would show 25000 instead which is average of January and feburary.

Anyone knows how to solve this issue?

Thanks in advance

[Sahir_Maharaj · Accepted solution]

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)
    )

[danextian · Accepted solution]

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.

[Anonymous · Accepted solution]

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.

[danextian · Accepted solution]

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.

[Sahir_Maharaj · Accepted solution]

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)
    )