Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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's avatar
    Anonymous
    6 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's avatar
Anonymous
Not applicable
6 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:

REGIONYearQuarterMonthWBYearMonthRank
BXL2018Qtr 1February392018021
BXL2018Qtr 2April1262018042
BXL2018Qtr 2May2762018053
BXL2018Qtr 3September182018094
BXL2019Qtr 4October132019105
BXL2019Qtr 4December432019126
BXL2020Qtr 2May452020057
BXL2020Qtr 2June72020068
BXL2020Qtr 3July282020079
BXL2020Qtr 3August620200810


Is that possible?

Thanks a lot.

KR,
A.

Anonymous's avatar
Anonymous
Not applicable
6 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