cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

I would greatly appreciate your help.

1 ACCEPTED SOLUTION
Community Support

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

2 REPLIES 2
Helper III

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

Community Support

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

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

Top Solution Authors
Top Kudoed Authors