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?
Hello fhill
That first formula worked like a charm.
I'm looking for the second option you provided for the average. I'm getting a error with the formula though. Any ideas?
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)
You my friend, a genius! Thank you! What a journey haha.
One last thing... is it possible to turn that first formula you did into telling me what the price was on that date?
Orginal formula =
CALCULATE(LASTDATE(tbReceivalHistory[Document Date]),FILTER(ALLEXCEPT(tbReceivalHistory,tbReceivalHistory[Part Number (Material)]),1=1))I want this to tell me the piece price on that date it's finding.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?Thank you again! This will do just what I need. I would imagine the average would be the best practice. You're a life saver. Thank you again for all your help!