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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

4 REPLIES 4
daXtreme
Solution Sage
Solution Sage

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.