Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am looking to calculate the average of previous year and the only date not showing correctly is 2021. I used an averagex function to determine the average cost(I've also tried using the divide function). (2021 is the only year not showing correctly for previous year, when i select 2021 using a filter for "Average Cost Per Part" is correct)
Average Cost Per Part=
AVERAGEX(
'All Spend Data',
'All Spend Data'[Ext Price]**
)
---------------------------------
Previous Year Average Cost =
CALCULATE(
[Average Cost Per Transaction],
DATEADD('Calendar'[Date], -1, YEAR)
)
Solved! Go to Solution.
Hi, this works if I have the time intelligence "auto date/time" is checked on but the previousyear worked without it! Thanks for your help!
Hi @Anonymous ,
Please update the formula of measure [Previous Year Average Cost] as below and check if it can get the correct result... You can find the details in the attachment.
Previous Year Average Cost =
CALCULATE (
[Average Cost Per Part],
FILTER (
ALLSELECTED ( 'All Spend Data' ),
YEAR ( 'All Spend Data'[Date] )
= SELECTEDVALUE ( 'All Spend Data'[Date].[Year] ) - 1
)
)
If the above one can't help you get the desired result, please provide some sample data in your table 'All Spend Data' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi, this works if I have the time intelligence "auto date/time" is checked on but the previousyear worked without it! Thanks for your help!
insted of DATEADD use PREVIOUSYEAR
Appreciate the help, I got so stuck in using the dateadd and forgot about the previousyear; which worked perfectly. Thanks for your help!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
106 | |
105 | |
77 | |
35 | |
35 |
User | Count |
---|---|
167 | |
107 | |
72 | |
66 | |
55 |