Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tracytran91
Helper III
Helper III

Rolling average MTD last 3 months

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.

 

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

1.png

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.

2.png

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. 

 

View solution in original post

2 REPLIES 2
tracytran91
Helper III
Helper III

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

v-rzhou-msft
Community Support
Community Support

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.

1.png

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.

2.png

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. 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.