Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!
Solved! Go to 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.
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!
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
Proud to be a Super User!
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
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.
I reworked my solution and used your proposal, as mine was less elegant 😉 thanks!
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |