Past 3 month rolling average using a measure

[meg222]

Hi,

I am trying to show my data as a rolling average for the past 3 months. I have a measure called '% Aware' which calculates the % of a column called 'Awareness'. DAX code below (Sheet1 and Sheet2 are linked by UNIQUEID 1 to 1 relationship).

% Aware = CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), 'Sheet2'[Awareness] = "Seelcted") / DISTINCTCOUNT('Sheet1'[UNQUEID])

This works perfectly to show the % of awareness each month when made into a line graph with month in the x-axis and % aware in the y-axis.

What I now need is this same line graph but showing a rolling average of the past 3 months for each month. I've tried various methods but haven't found any which work with a measure. The problem is that as soon as I add month into the x-axis the rolling average gets filtered by each month (and therefore isn't including the data for the previous 2). I need the % for "Jul-22" to be the average % of the past 3 months. This DAX code seems to work but as soon as I add month to the x-axis it reverts to showing the % aware for each month, not the rolling average.

Awareness moving average =  var p3m = DATESINPERIOD ( 'Sheet1'[Period - month], MAX ('Sheet1'[Period - month] ), -3, MONTH )

RETURN

CALCULATE (DISTINCTCOUNT('Sheet1'[UNQUEID]), 'Sheet2'[Awareness] = "Selected", p3m) / CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), p3m)

I can calculate the rolling average by creating a measure for each month but then I can't plot these into the y-axis of a line chart. Also the months go back to 2017 so it would mean creating a huge number of measures 

Aware Jun-22 =

var n = CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), 'Sheet2'[Awareness] = "Selected", ('Sheet1'[Period - month_new] = "Jun-22" || 'Sheet1'[Period - month_new] = "May-22" || 'Sheet1'[Period - month_new] = "Apr-22"))

var base_n = CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]),('Sheet1'[Period - month_new] = "Jun-22" || 'Sheet1'[Period - month_new] = "May-22" || 'Sheet1'[Period - month_new] = "Apr-22"))

RETURN n/base_n

Is this possible to do? Ultimately it's not the end of the world if I have to make a bar chart instead of a line chart but I need to show P3M rolling average for each month.

Thanks in advance,

[daXtreme · Accepted solution]

Please refer to this page for details about how to calculate rolling averages.

To whet your appetite...

[meg222]

Thank you! I got that to work using the method in the link.

I just have one further thing I need some help with which is that for the first two months I need the values to be blank - in the below image it would be Oct 2021 and Nov 2021 which should be blank as they aren't showing the past 3 month rolling average (Nov 2021 is an average of Oct-21 and Nov-21 data, Oct 2021 is just the average for Oct-21).

There is a date slicer on the page for the user to select the date range to show in the chart. I just need the chart to show nothing for whatever the first two months are which are selected in the slicer - so the line would start from the 3rd month onwards. 

This is the DAX measure for total awareness. I'm not too sure where to start with adding in something to omit the first two months or if it would be easier to make a separate measure perhaps?

Total Awareness =

VAR MonthsInRange = 3

VAR LastMonthRange =

    MAX ( 'Date'[Year Month Number] )

VAR FirstMonthRange =

    LastMonthRange - MonthsInRange + 1

VAR Period3M =

    FILTER (

        ALL ( 'Date'[Year Month Number] ),

        'Date'[Year Month Number] >= FirstMonthRange

            && 'Date'[Year Month Number] <= LastMonthRange

    )

VAR Result =

    IF (

        COUNTROWS ( Period3M ) >= MonthsInRange,

        CALCULATE (

            AVERAGEX ( Period3M, [% Aware] ),

            REMOVEFILTERS ( 'Date' )

        )

    )

RETURN

    Result

Any help much appreciated!

[daXtreme]

// First of all, you should number your months
// throughout all years starting with 1 and
// moving by one till the very last month in
// the calendar. Do not use Year Month Number
// which I believe resets every year and even
// if not, I guess it's not consecutive. You
// have to have a field that numbers all your
// months consecutively and does not reset
// when a new year starts. Let's say such a field
// is called MonthID (because this is what it
// really stands for).

// You have to put fields from the Date table
// onto the x-axis for this to work. You should
// never ever use fields from fact tables in
// any of your visuals. This is only allowed
// if you troubleshoot. Users should never be
// able to slice data via fact table's fields.
// Best Practice? All such fields should be
// hidden. Even more, the fact tables should
// always be hidden. For measures, a separate
// table should be created without any fields
// and measures should be stored on this very
// table.

// Then you'll write:

Total Awareness =
VAR MonthsInRange = 3
// Get the last visible month's ID.
VAR LastMonthRange = MAX( 'Date'[MonthID] )
// The first month for the calculation will
// be the one with MonthID removed (MonthslnRange - 1)
// times since you need MonthsInRange months in the range.
VAR FirstMonthRange = LastMonthRange - (MonthsInRange - 1)
VAR ThePeriod = // don't hard-code numbers in the names
    FILTER(
        ALL( 'Date'[MonthID] ),
        FirstMonthRange <= 'Date'[MonthID]
        && 
        'Date'[MonthID] <= LastMonthRange
    )
VAR Result =
    IF(
        // This effectively makes sure that
        // you've got 3 full months available.
        COUNTROWS( ThePeriod ) = MonthsInRange,
        CALCULATE(
            AVERAGEX(
                ThePeriod,
                // Make sure this measure is correct!
                [% Aware]
            ),
            REMOVEFILTERS( 'Date' )
        )
    )
RETURN
    Result

[meg222]

I'm not sure how to add a Month ID column to the Date table. The Date table is a calculated table so I can't add a conditional column in power query editor which is what I would normally do. I copied the DAX code below from the link you sent.

Date =

VAR FirstFiscalMonth = 3   -- First month of the fiscal year

VAR MonthsInYear = 12      -- Must be 12 for GranularityByDate

                           -- can be different for GranularityByMonth

VAR CalendarFirstDate = MIN ( Sheet1[Period - month]  )

VAR CalendarLastDate = MAX ( Sheet1[Period - month]  )

VAR CalendarFirstYear = YEAR ( CalendarFirstDate )

VAR CalendarFirstMonth = MONTH ( CalendarFirstDate )

VAR CalendarLastYear = YEAR ( CalendarLastDate )

VAR CalendarLastMonth = MONTH ( CalendarLastDate )

-------------------------

-- Internal calculations

-------------------------

VAR GranularityByDate =

    ADDCOLUMNS (

        CALENDAR (

            DATE ( CalendarFirstYear, CalendarFirstMonth, 1 ),

            EOMONTH (

                DATE ( CalendarLastYear, CalendarLastMonth, 1 ),

                0

            )

        ),

        "Year Month Number", YEAR ( [Date] ) * MonthsInYear

            + MONTH ( [Date] ) - 1

    )

VAR GranularityByMonth =

    SELECTCOLUMNS (

        GENERATESERIES (

            CalendarFirstYear * MonthsInYear + CalendarFirstMonth - 1

                - (MonthsInYear - 12) * (CalendarFirstMonth < FirstFiscalMonth),

            CalendarLastYear * MonthsInYear + CalendarLastMonth - 1

                - (MonthsInYear - 12) * (CalendarLastMonth < FirstFiscalMonth),

            1

        ),

        "Year Month Number", [Value]

    )

RETURN GENERATE (

    GranularityByDate,        -- Use GranularityByMonth to get one row for each month

    VAR YearMonthNumber = [Year Month Number]

    VAR FiscalMonthNumber =

        MOD (

            YearMonthNumber + 1

                * (FirstFiscalMonth > 1)

                * (MonthsInYear + 1 - FirstFiscalMonth),

            MonthsInYear

        ) + 1

    VAR FiscalYearNumber =

        QUOTIENT (

            YearMonthNumber + 1

                * (FirstFiscalMonth > 1)

                * (MonthsInYear + 1 - FirstFiscalMonth),

            MonthsInYear

        )

    VAR OffsetFiscalMonthNumber = MonthsInYear + 1 - (MonthsInYear - 12)

    VAR MonthNumber =

        IF (

            FiscalMonthNumber <= 12 && FirstFiscalMonth > 1,

            FiscalMonthNumber + FirstFiscalMonth

                - IF (

                    FiscalMonthNumber > (OffsetFiscalMonthNumber - FirstFiscalMonth),

                    OffsetFiscalMonthNumber,

                    1

                ),

            FiscalMonthNumber

        )

    VAR YearNumber = FiscalYearNumber - 1 * (MonthNumber > FiscalMonthNumber)

    VAR YearMonthKey = YearNumber * 100 + MonthNumber

    VAR MonthDate = DATE ( YearNumber, MonthNumber, 1 )

    VAR FiscalQuarterNumber = MIN ( ROUNDUP ( FiscalMonthNumber / 3, 0 ), 4 )

    VAR FiscalYearQuarterNumber = FiscalYearNumber * 4 + FiscalQuarterNumber - 1

    VAR FiscalMonthInQuarterNumber =

        MOD ( FiscalMonthNumber - 1, 3 ) + 1 + 3 * (MonthNumber > 12)

    VAR MonthInQuarterNumber = MOD ( MonthNumber - 1, 3 ) + 1 + 3 * (MonthNumber > 12)

    VAR QuarterNumber = MIN ( ROUNDUP ( MonthNumber / 3, 0 ), 4 )

    VAR YearQuarterNumber = YearNumber * 4 + QuarterNumber - 1

    RETURN ROW (

        "Year Month Key", YearMonthKey,

        "Year", YearNumber,

        "Year Quarter", FORMAT ( QuarterNumber, "\Q0" )

            & "-" & FORMAT ( YearNumber, "0000" ),

        "Year Quarter Number", YearQuarterNumber,

        "Quarter", FORMAT ( QuarterNumber, "\Q0" ),

        "Year Month", IF (

            MonthNumber > 12,

            FORMAT ( MonthNumber, "\M00" ) & FORMAT ( YearNumber, " 0000" ),

            FORMAT ( MonthDate, "mmm yyyy" )

        ),

        "Month", IF (

            MonthNumber > 12,

            FORMAT ( MonthNumber, "\M00" ),

            FORMAT ( MonthDate, "mmm" )

        ),

        "Month Number", MonthNumber,

        "Month In Quarter Number", MonthInQuarterNumber,

        "Fiscal Year", FORMAT ( FiscalYearNumber, "\F\Y 0000" ),

        "Fiscal Year Number", FiscalYearNumber,

        "Fiscal Year Quarter", FORMAT ( FiscalQuarterNumber, "\F\Q0" ) & "-"

            & FORMAT ( FiscalYearNumber, "0000" ),

        "Fiscal Year Quarter Number", FiscalYearQuarterNumber,

        "Fiscal Quarter", FORMAT ( FiscalQuarterNumber, "\F\Q0" ),

        "Fiscal Month", IF (

            MonthNumber > 12,

            FORMAT ( MonthNumber, "\M00" ),

            FORMAT ( MonthDate, "mmm" )

        ),

        "Fiscal Month Number", FiscalMonthNumber,

        "Fiscal Month In Quarter Number", FiscalMonthInQuarterNumber

    )

)