Rolling average MTD last 3 months

[tracytran91]

Hi friends, 

I have a date filter and the Rolling average MTD last 3 months measure. 

For example: if last date in sale table is 12 Jul, i would like to get the Rolling average sale from 1st to 12th Jun and from 1st to 12th May and 1st to 12th April. 

I created this measure:

Sales AVG MTD L3M = 
Var SaleL3M= CALCULATE([Total Sales],
            day(D_Date[Date])<day(TODAY()), 
        DATESINPERIOD(D_Date[Date], EOMONTH ( MIN ( D_Date[Date]), -1 ), -3, MONTH ))
Return DIVIDE(SaleL3M,3)

The measure works for the last date in sale table. However, if I use a date filter what allows the users choose a specific date, the measure does not work in this case. It works for month granularity only, What I want is it works also in date granularity.

Could anyone please help me to correct this code? 

I would greatly appreciate your help.

[tracytran91]

It works like charm!! thank for your kind help!

[v-rzhou-msft · Accepted solution]

Hi @tracytran91 

I think you need the help of an unrelated date table.

Here I build a sample to help you.

My sample is easy, it contains values from 2021/01/01 to 2021/07/15(Today).

Build an unrelated date table.

Date =
VAR _T1 =
    ADDCOLUMNS (
        CALENDAR ( MIN ( Sample2[Date] ), MAX ( Sample2[Date] ) ),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "YearMonth",
            YEAR ( [Date] ) * 100
                + MONTH ( [Date] )
    )
VAR _T2 =
    ADDCOLUMNS ( _T1, "Rank", RANKX ( _T1, [YearMonth],, ASC, DENSE ) )
RETURN
    _T2

Measure:

Total Sales = SUM(Sample2[Value])

Sales AVG MTD L3M = 
VAR _Selectvalue =
    IF ( ISFILTERED ( 'Date'[Date] ), SELECTEDVALUE ( 'Date'[Date] ), TODAY () )
VAR _StartDate =
    EOMONTH ( _Selectvalue, -3 ) + 1 /*Start date of the range*/
VAR _LastDay =
    EOMONTH ( _Selectvalue, 0 )
VAR _Day =
    DAY ( _Selectvalue )
VAR _Sumlastday =
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( Sample2 ),
            Sample2[Date] <= _Selectvalue
                && Sample2[Date] >= _StartDate
        )
    )
VAR _Sumnotlastday =
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( Sample2 ),
            Sample2[Date] <= _Selectvalue
                && Sample2[Date] >= _StartDate
                && DAY ( Sample2[Date] ) <= _Day
        )
    )
VAR _SUM =
    IF ( _Selectvalue = _LastDay, _Sumlastday, _Sumnotlastday )
VAR _Avg =
    DIVIDE ( _SUM, 3 )
RETURN
    _Avg

Result:

By default, there is no selection in slicer, this measure will return rolling 3 months average to today(2021/07/15).

So average is between sales from 2021/05/01 to 2021/05/15, 2021/06/01 to 2021/06/15 and 2021/07/01 to 2021/07/15.

If you select 2021/07/12, average is between sales from 2021/05/01 to 2021/05/12, 2021/06/01 to 2021/06/12 and 2021/07/01 to 2021/07/12.

In this measure if selectvalue or default value is the last day of the month, it will calculate the rolling 3 months average directly. Ex if you select 2021/06/30, average is between April , May and June whole three months.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.