Average purchase price from 2023

[RaMiRo87]

Hi all,

I've been searching the forum for my topic but haven't found anything related to my recent problem.

I have a data table named 'Purchase Price'. Here I'm calclulating different KPIs, for example the average of the purchase price for the fiscal year 2023 for every product. For this I'm using the column [Purchase Price (Accounting) Inventory], the date column [CreatedOn] as well as the column for the product or item nr. [ItemId]. I'll show you how it looks like and give you a simplified example:

ItemId	CreatedOn	Purchase Price (Accounting) Inventory	Average Purchase Price (Accounting) 2023
1234	12.05.2022	70
1234	01.12.2023	50	30
1234	09.10.2023	10	30

The last two of the three records should be considered for calclulating the average because they are booked in 2023.

The column [Average Purchase Price (Accounting) 2023] is my desired solution but I would be glad about any working solution to be honest. 

Please let me know if you need any other information or detail of the data. 

Thank you in advance! Ramirez

[bhanu_gautam · Accepted solution]

@RaMiRo87 , You can create a measure for this using

Average Purchase Price (Accounting) 2023 =
CALCULATE(
AVERAGE('Purchase Price'[Purchase Price (Accounting) Inventory]),
YEAR('Purchase Price'[CreatedOn]) = 2023
)

Please accept as solution and give kudos if it helps

[Anonymous · Accepted solution]

Hi @RaMiRo87 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure.

Average Purchase Price (Accounting) 2023 = 
IF(YEAR(MAX('Purchase Price'[CreatedOn]))=2023,CALCULATE(AVERAGE('Purchase Price'[Purchase Price (Accounting) Inventory]),YEAR('Purchase Price'[CreatedOn]) = 2023),BLANK())

(3)Or we can create a calculated column.

_Average Purchase Price (Accounting) 2023 =
IF (
    YEAR ( 'Purchase Price'[CreatedOn] ) = 2023,
    CALCULATE (
        AVERAGE ( 'Purchase Price'[Purchase Price (Accounting) Inventory] ),
        FILTER ( 'Purchase Price', YEAR ( 'Purchase Price'[CreatedOn] ) = 2023 )
    ),
    BLANK ()
)

(4) Then the result is as follows.

Best Regards,

Neeko Tang

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

[RaMiRo87]

Thank you very much for your well structured solution! Got everything I needed and my own Measure that I've prepared before posting this comes close. 🙂 

Thanks and kind regards

[bhanu_gautam · Accepted solution]

@RaMiRo87 , You can create a measure for this using

Average Purchase Price (Accounting) 2023 =
CALCULATE(
AVERAGE('Purchase Price'[Purchase Price (Accounting) Inventory]),
YEAR('Purchase Price'[CreatedOn]) = 2023
)

Please accept as solution and give kudos if it helps