Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

How to create rolling averages over non-consecutive dates?

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

Need Help

Tips and Tricks

Anonymous

5 years ago

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

Anonymous

Not applicable

5 years ago

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

Not applicable

5 years ago

Hi Anonymous

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.

Anonymous

Not applicable

5 years ago

Hi Anonymous ，

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

Forum Discussion

How to create rolling averages over non-consecutive dates?

Related content

Rolling 12M Average measure

Rolling 6 month average with non-consecutive dates

Rolling accumulation

3 Day Rolling Average - DAX

Rolling accumulation