Calculating quarterly historical average

[mgarcianxp]

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

[mgarcianxp]

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: 

Q1 DOI Average = CALCULATE(AVERAGE('|Table OEM'[OEM DOI]),ALLEXCEPT('|Table OEM','|Table OEM'[Date].[Quarter]),AND('|Table OEM'[Qtr]="Q1",'|Table OEM'[Date] <= DATE(2019,12,31)))

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

DOI Q1 Avrg. = CALCULATE(AVERAGEX('|Measures OEM','|Measures OEM'[DOI per OEM]),ALLEXCEPT('|Calendar','|Calendar'[Date].[Quarter]),AND('|Calendar'[Qtr]="Q1",'|Calendar'[Date] <= DATE(2019,12,31)))

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

[some_bih]

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

[mgarcianxp]

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 

[v-rzhou-msft · Accepted solution]

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.

[mgarcianxp]

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

[mgarcianxp]

Hi @v-rzhou-msft , 

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