How to create rolling averages over non-consecutive dates?

[Anonymous]

Hi community,
I 'm relatively new to power BI and I 'm stuck with rolling averages.
I would like to create a 3-month rolling average starting from month-1 until month+1.
the built-in quick measure works fine, BUT it includes ALL months in my calendar.
My purpose would be to "skip" months for which I don't have data in my main table.

Rolling average quick measure:

WB rolling average =
IF(
ISFILTERED('Calendar CMP'[Date]),
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('Calendar CMP'[Date].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Calendar CMP'[Date].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -1, MONTH)),
ENDOFMONTH(DATEADD(__LAST_DATE, 1, MONTH))
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Calendar CMP'),
'Calendar CMP'[Date].[Year],
'Calendar CMP'[Date].[QuarterNo],
'Calendar CMP'[Date].[Quarter],
'Calendar CMP'[Date].[MonthNo],
'Calendar CMP'[Date].[Month]
),
__DATE_PERIOD
),
CALCULATE(SUM('Global'[WB]), ALL('Calendar CMP'[Date].[Day]))
)
)


PBI output:

REGION	Year	Quarter	Month	WB	WB rolling average
BXL	2018	Qtr 1	January		39
BXL	2018	Qtr 1	February	39	39
BXL	2018	Qtr 1	March		83
BXL	2018	Qtr 2	April	126	201
BXL	2018	Qtr 2	May	276	201
BXL	2018	Qtr 2	June		276
BXL	2018	Qtr 3	August		18
BXL	2018	Qtr 3	September	18	18
BXL	2018	Qtr 4	October		18
BXL	2019	Qtr 3	September		13
BXL	2019	Qtr 4	October	13	13
BXL	2019	Qtr 4	November		28
BXL	2019	Qtr 4	December	43	43
BXL	2020	Qtr 1	January		43
BXL	2020	Qtr 2	April		45
BXL	2020	Qtr 2	May	45	26
BXL	2020	Qtr 2	June	7	27
BXL	2020	Qtr 3	July	28	14
BXL	2020	Qtr 3	August	6	17
BXL	2020	Qtr 3	September		6

Desired:

REGION	Year	Quarter	Month	WB	WB rolling average
BXL	2018	Qtr 1	February	39	83
BXL	2018	Qtr 2	April	126	147
BXL	2018	Qtr 2	May	276	140
BXL	2018	Qtr 3	September	18	102
BXL	2019	Qtr 4	October	13	25
BXL	2019	Qtr 4	December	43	34
BXL	2020	Qtr 2	May	45	32
BXL	2020	Qtr 2	June	7	27
BXL	2020	Qtr 3	July	28	14
BXL	2020	Qtr 3	August	6	17

Much appreciated.

KR,

A.

[v-yiruan-msft]

Hi @Anonymous ,

I created a sample pbix file for you, you can get it from this link. The implementation steps are as follows：

1. Create a calculated column to get YearMonth

YearMonth = CONCATENATE(Year('Global'[Date]),FORMAT('Global'[Date],"mm"))

2. Create a calculated column to rank the data by YearMonth

Rank = RANKX(ALL('Global'),'Global'[YearMonth],,ASC,Dense)

3. Create a measure as below to get the rolling averages

WB rolling average = 
VAR _currank =
    MAX ( 'Global'[Rank] )
VAR _countofMonth =
    CALCULATE (
        DISTINCTCOUNT ( 'Global'[YearMonth] ),
        FILTER (
            ALL ( 'Global' ),
            'Global'[Rank] >= _currank - 1
                && 'Global'[Rank] <= _currank + 1
        )
    )
VAR _sumofWB =
    CALCULATE (
        SUM ( 'Global'[WB] ),
        FILTER (
            ALL ( 'Global' ),
            'Global'[Rank] >= _currank - 1
                && 'Global'[Rank] <= _currank + 1
        )
    )
RETURN
    DIVIDE ( _sumofWB, _countofMonth )

Best Regards

Rena

[Anonymous]

Hi @v-yiruan-msft 

Thanks for you reply.

Unfortunately, it doesn't render me the output I expected.
Instead of ranking all yearmonths in the 'Global' Table, I would like to have more something like this, so I can calculate the rolling overages over non-consecutive months:

REGION	Year	Quarter	Month	WB	YearMonth	Rank
BXL	2018	Qtr 1	February	39	201802	1
BXL	2018	Qtr 2	April	126	201804	2
BXL	2018	Qtr 2	May	276	201805	3
BXL	2018	Qtr 3	September	18	201809	4
BXL	2019	Qtr 4	October	13	201910	5
BXL	2019	Qtr 4	December	43	201912	6
BXL	2020	Qtr 2	May	45	202005	7
BXL	2020	Qtr 2	June	7	202006	8
BXL	2020	Qtr 3	July	28	202007	9
BXL	2020	Qtr 3	August	6	202008	10

Is that possible?

Thanks a lot.

KR,
A.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ，

I just updated my sample pbix file, please check whether that is what you want. According to your reply, it seems that you don't want to create additional calculated columns to achieve it, so I create another new measure to get the rolling average for non-consecutive dates without creating any other measure or calculated column:

New_WB rolling average = 
VAR _mindate =
    CALCULATE ( MIN ( 'Global'[Date] ), ALL ( 'Global' ) )
VAR _maxdate =
    CALCULATE ( MAX ( 'Global'[Date] ), ALL ( 'Global' ) )
VAR _curmonth =
    CONCATENATE (
        YEAR ( MAX ( 'Global'[Date] ) ),
        FORMAT ( MAX ( 'Global'[Date] ), "mm" )
    )
VAR _premdate =
    CALCULATE (
        MAX ( 'Global'[Date] ),
        FILTER (
            ALL ( 'Global' ),
            CONCATENATE ( YEAR ( 'Global'[Date] ), FORMAT ( 'Global'[Date], "mm" ) ) < _curmonth
        )
    )
VAR _premonth =
    CONCATENATE ( YEAR ( _premdate ), FORMAT ( _premdate, "mm" ) )
VAR _nextmdate =
    CALCULATE (
        MIN ( 'Global'[Date] ),
        FILTER (
            ALL ( 'Global' ),
            CONCATENATE ( YEAR ( 'Global'[Date] ), FORMAT ( 'Global'[Date], "mm" ) ) > _curmonth
        )
    )
VAR _nextmonth =
    CONCATENATE ( YEAR ( _nextmdate ), FORMAT ( _nextmdate, "mm" ) )
VAR _sumofWB =
    CALCULATE (
        SUM ( 'Global'[WB] ),
        FILTER (
            ALL ( 'Global' ),
            CONCATENATE ( YEAR ( 'Global'[Date] ), FORMAT ( 'Global'[Date], "mm" ) ) >= _premonth
                && CONCATENATE ( YEAR ( 'Global'[Date] ), FORMAT ( 'Global'[Date], "mm" ) )
                    <= IF (
                        _nextmonth = "",
                        CONCATENATE ( YEAR ( _maxdate ), FORMAT ( _maxdate, "mm" ) ),
                        _nextmonth
                    )
        )
    )
RETURN
    DIVIDE (
        _sumofWB,
        IF (
            _curmonth = CONCATENATE ( YEAR ( _mindate ), FORMAT ( _mindate, "mm" ) )
                || _curmonth = CONCATENATE ( YEAR ( _maxdate ), FORMAT ( _maxdate, "mm" ) ),
            2,
            3
        ),
        0
    )

Best Regards

Rena