DAX Rolling 3 months by periods that are not the exact month dates

[ofeliajesus]

Hello,

I have a fact table and a date dimension joined by a date

The dim_date table is like the one below, the Period column divides the dates and isn't by exact months. Example the Period
2021_04 Starts on 05/04/2021 and finishes on 02/05/2021 and it is April
2021_05 Starts on 02/05/2021 and finishes on 30/05/2021 and it is May

I want a rolling 3 Months DAX Measure by Period
I created a column Financial Date with the last day of the month so for the period
2021_04 I have 30/04/2021 from 05/04/2021 until 02/05/2021
2021_05 I have 31/05/2021 from 03/05/2021 until 30/05/2021

I already did some DAX but because I used the Period to filter everything went ok the problem is to roll the 3 months to calculate the average I am thinking to do it by Financial Date with DATESINPERIOD

I started with
VAR Lastselecteddate = MAX(Dim_Date[FinancialDATE])

VAR Result =
AVERAGEX(
VALUES(Dim_Date[Financial Year Month]), Dim_Date[Amount ]
)
Return
COUNTROWS(DATESINPERIOD(Dim_Date[Date],Lastselecteddate,-3,MONTH))

What I want is the next rolling 3 months average by period
2021_04
2021_05
2021_06
and so on but because I defined this period with Financial Date one period is one "fake" month

Do you have some clues on how can I do this?

Thank you 

[daXtreme]

Just number the months from 1 to whatever their number (each month in each year must be considered different, no two will have the same number, of course), consecutively, and use this very number to calculate the average. That's the easiest way. If the max month number in the current context is, say, 20, then it'll be easy to refer to months like 18, 19, 20, to calculate the 3-month lagging average. If you want another average, just manipulate the current month number as explained above.

[ofeliajesus]

Can you give me some clue about the DAX formula I need to manipulate with the measure below ... On top of it, I need to apply the
rolling 3 periods starting in 2021_04
The Output will be
2021_06 - 2021_04 + 2021_05 + 2021_06 = (17+15+9)/3
2021_07 - 2021_05 + 2021_06 + 2021_07 = (15+9+16)/3
and so on

Quotes per Period =
VAR tblNumOfWeeksInPeriod=
SUMMARIZE(
Dim_Date
,Dim_Date[Period]
,Dim_Date[# of Weeks]
)

VAR SuMOfWeeksInPeriod = SUMX(tblNumOfWeeksInPeriod,Dim_Date[# of Weeks])

Var quotesperweek =
MROUND(
DIVIDE(
'Table Measures'[Total Region]
,SuMOfWeeksInPeriod
)
,1
)
Return
quotesperweek

[daXtreme · Accepted solution]

// Say you want to calculate a 3-period rolling avg
// of a measure. Name the measure [M]. The periods
// must have consecutive numbers in the calendar,
// so that we can move through them using simple
// arithmetic. Let the sequence of periods start at 1
// and increment by 1. Let the field be named
// PeriodSeqno.

[M 3-Period Rolling Avg] =
// Set the number of periods to calculate
// the average over.
var PeriodCount = 3
// First, get the last visible period.
var LastPeriodSeqno = MAX( Dim_Date[PeriodSeqno] )
// Then, get the periods over which to calc.
var PeriodsToAverageOver =
    CALCULATETABLE(
        DISTINCT( Dim_Date[PeriodSeqno] ),
        // Here's the place where you use the fact that
        // all the periods are consecutively numbered.
        // In fact, the counting does not have to start
        // at 1 but it has to increment by 1.
        Dim_Date[PeriodSeqno] <= LastPeriodSeqno,
        Dim_Date[PeriodSeqno] > LastPeriodSeqno - PeriodCount,
        REMOVEFILTERS( Dim_Date )
    )
// We need to make sure that there are indeed
// PeriodCount periods in the set. Otherwise,
// the average will not be correct. This could happen
// if we were too close to the beginning of the calendar.
var ShouldCalculate =
    COUNTROWS( PeriodsToAverageOver ) = PeriodCount
var Result =
    if( ShouldCalculate,
        CALCULATE(
            AVERAGEX(
                PeriodsToAverageOver,
                [M]
            ),
            REMOVEFILTERS( Dim_Date )
        )
    )
return
    Result

[adelinadamian]

@daXtreme thanks for this!! very nice solution! you helped me with part of my headaches in the last days.