Forum Discussion
Weight Average
This the DateAdd (-1 Year) option instead of the DATE command..... (Sorry for the change in TABLE name and columns in the version below vs. my original post.) But you only have to change the FILTER Portion to use DATEADD vs. DATE(Y)(M)(D)
1_Year_Avg_Spend = CALCULATE( SUM( 'Table'[USD Spend]) / SUM('Table'[Qty]), FILTER( 'Table', 'Table'[Document Date] > DATEADD('Table'[Document Date], -1, YEAR)))Try this....
Last_Pur_Price = CALCULATE( AVERAGE( 'Table'[Part Price]), FILTER('Table', 'Table'[Document Date] = [Last_Pur_Date]))If you had multiple sales on the same day, assuming you want the average of the two?
Just saw you updated sample, please let me know if the code I just posted helps you get to your solution without me having to re-do all the code.
Thank You,
Forrest
Is you Date column in the original data formatted to be a Date Value in Power BI?
FOrrest
Hello,
Yes it is.
So the measure works when I have all material numbers. When I filter down to just one of them I get the error. Any way to stop this?
Hmmmm, I'm going to promote this up on the channel. I took your longer sample data, changed every other Part Number, but was still able to produce a calcualtion with 1 Part Number filtered...