Using 12 month Average price as part of a calculation for this month

[Rygaard]

I need to predict price for "Tones we have recived but not priced"

We recive "item a" today, but the price is based on a chemical analysis that will not be ready for the next 4 weeks.

So to give an estimate i want to calculate the Avarage price for "Item a" 

What i have

• Normal Avarage price  ['Avg Price] for items that has been priced ([Reveneu] / [ton])
• Avg price Rolling Avarage last 12 m (Quickmeassure)
• Tonnes 

But each time i try to use it on a graph with months on X axis the avarage is only covering prices from this month, witch is what you normaly want but not in this case - the month filthering from the graph should not apply to the calculation.

So fare the only solution i have is to make a new calculated column with the avarage price L12M - and use that price.

what is the Best way to do this  (performance wise)

[amitchandak]

@Rygaard , if you have separate date table, you should be able to get price like

Assume you have price measure

Rolling 12 Avg = calculate(AverageX(Values('Date'[MONTH Year]),[Price]) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Rygaard · Accepted solution]

@amitchandak 

There are 2 conserns with your Aproach...

1) its not a weighted Avarage  (this can be easy solved by using SumX for first price then ton and then Divide)

2) it is still tied to a date, so if i select lets say 2040-01-01 I still want it to show the Avarage price for the last 12 mont from today - no matter the date the Avarage price should be the Current L12M.

Went a different route

I have added a new table on DW, aggrigrating ton and Reveneu for the past 12 month, Grouped by :{Item,Department,Customer}  

Im sure this could also be done with a virituel table but im worried about performance so this seemed like the best way to do it.