Skip to main content
cancel
Showing results for 
Search instead 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

Reply
pablofernandez
New Member

End Of Quarter Moving Average of five previous n quarters at the closing

Hello Guys, 

 

I am having a big issue trying to get an average of the last endofquarter of the past n quarters ( I want past 5 quarters but It could change)

 

I need that if the quarter doesn´t end the value should take the value only for quarters ended. I.e: If I am in November, the values should be the ones from September, June, March, December (Previous Year), September (Previous Year).

I´ve went with the pattern of moving average, mixing with the endofquarter function. But it is not working properly. Any advice on how I could workorund this issue? Or where my asumption is not right. 

 

VAR NumOfQuarters = 5
VAR LastCurrentDate = MAX ( 'Dates'[Dates] )
VAR
Period = DATESINPERIOD ( 'Dates'[Dates], LastCurrentDate, - NumOfQuarters, QUARTER )
VAR Result =
CALCULATE (
SUMX (
VALUES ( 'Dates'[Month] ),
ENDOFQUARTER([Sales],Dates[Quarter])
),
Period
)
VAR FirstDateInPeriod = MINX ( Period, 'Dates'[Dates] )
VAR LastDateWithSales = MAX (FinalData[Date] )
RETURN
IF ( FirstDateInPeriod <= LastDateWithSales, Result)
1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @pablofernandez 

Try this,

Dax code:

Measure = //get total of the last endofquarter of the past 5 quarters
    var _NumOfQuarters = -5
    var _CurrentDate=DATE(2021,11,2)//TODAY()//If I am in 2021-11-2. you can change it later according to your needs.
    var _Start= STARTOFQUARTER(DATESINPERIOD('calendar'[Date],_CurrentDate,_NumOfQuarters,QUARTER))
    var _End= STARTOFQUARTER(DATESINPERIOD('calendar'[Date],_CurrentDate,1,QUARTER))
return CALCULATE(SUM(Data[Value]),FILTER(ALL(Data),Data[Date]>=_Start && Data[Date]<_End && MONTH(Data[Date]) in {3,6,9,12}))

vxiaotang_2-1640585292956.png

Average = 
    var _NumOfQuarters = -5
    var _CurrentDate=DATE(2021,11,2)//TODAY()//
    var _Start= STARTOFQUARTER(DATESINPERIOD('calendar'[Date],_CurrentDate,_NumOfQuarters,QUARTER))
    var _End= STARTOFQUARTER(DATESINPERIOD('calendar'[Date],_CurrentDate,1,QUARTER))
return Divide(CALCULATE(SUM(Data[Value]),FILTER(ALL(Data),Data[Date]>=_Start && Data[Date]<_End && MONTH(Data[Date]) in {3,6,9,12})),ABS(_NumOfQuarters))

vxiaotang_3-1640585358286.png

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @pablofernandez 

Try this,

Dax code:

Measure = //get total of the last endofquarter of the past 5 quarters
    var _NumOfQuarters = -5
    var _CurrentDate=DATE(2021,11,2)//TODAY()//If I am in 2021-11-2. you can change it later according to your needs.
    var _Start= STARTOFQUARTER(DATESINPERIOD('calendar'[Date],_CurrentDate,_NumOfQuarters,QUARTER))
    var _End= STARTOFQUARTER(DATESINPERIOD('calendar'[Date],_CurrentDate,1,QUARTER))
return CALCULATE(SUM(Data[Value]),FILTER(ALL(Data),Data[Date]>=_Start && Data[Date]<_End && MONTH(Data[Date]) in {3,6,9,12}))

vxiaotang_2-1640585292956.png

Average = 
    var _NumOfQuarters = -5
    var _CurrentDate=DATE(2021,11,2)//TODAY()//
    var _Start= STARTOFQUARTER(DATESINPERIOD('calendar'[Date],_CurrentDate,_NumOfQuarters,QUARTER))
    var _End= STARTOFQUARTER(DATESINPERIOD('calendar'[Date],_CurrentDate,1,QUARTER))
return Divide(CALCULATE(SUM(Data[Value]),FILTER(ALL(Data),Data[Date]>=_Start && Data[Date]<_End && MONTH(Data[Date]) in {3,6,9,12})),ABS(_NumOfQuarters))

vxiaotang_3-1640585358286.png

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

pablofernandez
New Member

Thank you but it isn´t working. The formula, I think, should use closingbalancequarter for using the last value of the quarter, I think I can use the moving average pattern to go back but I don´t know how to tell closingbalance quarter to only carry the closing balance I.e If it is October the last month take the previous 5 Quarterts and do the average... 

 

Many thanks for your help... I have deadlines and I am a little bit... 

amitchandak
Super User
Super User

@pablofernandez , Try a meausre like

 

Rolling 5 = CALCULATE(AverageX(values(Date[Quarter Year]), calculate(lastnonblankvalue('Date'[Date]), Sum(Table[Value]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-5,Quarter))

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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