topic Re: Calculating quarterly historical average in DAX Commands and Tips

Calculating quarterly historical average

mgarcianxp — Mon, 12 Jun 2023 16:28:34 GMT

I am new to Power BI, and dont have much DAX experience. I've tried to find a post that helps me with this, but have not found any.

I am trying to replicate the excel chart below in PowerBI and I am having trouble to write the DAX code for the historical average (red line).

The DOI values are not in a table but they are a measure. The historical average is supposed to be the 5-years quarterly seasonality: Per quarter: average of the DIO from 2015 to 2019. So basically, I need to calculate 4 values and repeate them on each quarters for all years.

Thanks for the help!!

Re: Calculating quarterly historical average

some_bih — Mon, 12 Jun 2023 21:40:15 GMT

Hi mgarcianxp check link for rolling average (monthly) and adjust for your requirements (quarterly)

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

I hope this help

Re: Calculating quarterly historical average

mgarcianxp — Tue, 13 Jun 2023 15:31:06 GMT

Hi some_bih ,

thank you for the answer.

I am not looking for the rolling average, but the historical average is static. Anyway I have solved it in a probabl not very efficien way, by creating a calculated table instead of a measuers table, then filtering it 4 times for the 4 quarters to calculate the average of each quarter, so I can have a measure that repeats itselve thoruout the yerars.

Not very elegant, but it's working 🙂

thansk again

Re: Calculating quarterly historical average

Anonymous — Wed, 14 Jun 2023 07:35:18 GMT

Hi mgarcianxp ,

I suggest you to try to create a CALENDAR table to help calculation.

Calendar = ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ), "Quarter", QUARTER ( [Date] ), "Month", MONTH ( [Date] ) )

Create a relationship between 'Calendar'[Date] and 'Fact Table'[Date].

AVERAGE BY QUARTER = AVERAGEX ( ALLEXCEPT ( Calendar, Calendar[Year], Calendar[Quarter] ), 'Fact Table'[DIO] )

If this reply still couldn't help you solve your issue, please share a sample file with me.

Best Regards,
Rico Zhou

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

Re: Calculating quarterly historical average

mgarcianxp — Thu, 15 Jun 2023 09:36:10 GMT

Hi Anonymous ,

thanks for the reply, I managed to solve the issue in a different way 🙂 #

Re: Calculating quarterly historical average

mgarcianxp — Thu, 15 Jun 2023 11:59:02 GMT

I reworked my solution and used your proposal, as mine was less elegant 😉 thanks!

Re: Calculating quarterly historical average

mgarcianxp — Thu, 15 Jun 2023 17:05:55 GMT

Hi again,

still struggling with the average of a measure.

I solved the issue above by creating a table instead of working with measures. This is working fine for the total values (aggregate of all the companies). But I still want to do it without the additional table.

This is how the average is calculated in the case where DOI is a column:

The Average is calculated as follows:

This is the calculation in the case where DOI is a measure:

The both tables below show the results for both cases. How can I get the results of the left table using the measure? Thanks!