cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ofeliajesus
Helper I
Helper I

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

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?

 

 

ofeliajesus_0-1656711551521.png

 

Thank you 

 

1 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

View solution in original post

3 REPLIES 3
daXtreme
Super User
Super User

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.

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

// 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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors