DATEADD Function

[Anonymous]

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

[lukiz84 · Accepted solution]

insted of DATEADD use PREVIOUSYEAR

[v-yiruan-msft]

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

[Anonymous · Accepted 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!

[lukiz84 · Accepted solution]

insted of DATEADD use PREVIOUSYEAR

[Anonymous]

Appreciate the help, I got so stuck in using the dateadd and forgot about the previousyear; which worked perfectly. Thanks for your help!