topic Re: DAX Rolling 3 months by periods that are not the exact month dates in DAX Commands and Tips

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

ofeliajesus — Fri, 01 Jul 2022 21:40:29 GMT

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

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

daXtreme — Sat, 02 Jul 2022 08:14:23 GMT

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.

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

ofeliajesus — Tue, 05 Jul 2022 10:11:19 GMT

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

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

daXtreme — Tue, 05 Jul 2022 16:45:14 GMT

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

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

adelinadamian — Fri, 14 Oct 2022 14:18:44 GMT

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