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 ,
So organized the data below how we would expect it to be set up.
Purchase 1 - PN-12345 Spend 500 Quantity 50 Date 10/1/2020
Purchase 2 - PN-12345 Spend 400 Quantity 40 Date 1/1/2020
Purchase 3 - PN-12345 Spend 600 Quantity 60 Date 5/1/2020
Purchase 5 - PN-12345 Spend 100 Quantity 10 Date 6/1/2019
So for the data above. The calc would only take the purchases that fall into the 365 date range from the most recent purchase (purchase 1). Thus purchase 5 would fall out of the average since its outside of that timeline. The calc for the weighted average would just be during that 365 day timeframe. Take the sum of spend and divide it by the sum of the quanitity.
Let me know if this helps
Hi,
Share some data to work with. The data should be such that can be pasted in an MS Excel file. Alternatively, share the link from where i can download your PBI file.
Ashish_Mathur Let me know if this helps
Part Number (Material) Part Price (USD) (Piece Price) Quantity UOM (Unit of Measure) USD Spend Document Date PN-272679 $35.05 9 EA $315.42 3/14/2019 PN-272679 $23.54 3 EA $70.63 4/1/2019 PN-272679 $21.37 96 EA $2,051.78 4/1/2019 PN-272679 $23.54 30 EA $706.28 4/1/2019 PN-272679 $34.88 9 EA $313.90 4/15/2019 PN-272679 $21.62 69 EA $1,491.70 4/16/2019 PN-272679 $21.57 75 EA $1,617.49 4/29/2019 PN-272679 $24.94 24 EA $598.67 4/29/2019 PN-272679 $23.44 24 EA $562.52 5/10/2019 PN-272679 $24.83 36 EA $894.03 5/30/2019 PN-272679 $22.42 48 EA $1,076.22 5/30/2019 PN-272679 $25.13 24 EA $603.11 6/10/2019 PN-272679 $25.13 24 EA $603.11 6/27/2019 PN-272679 $25.13 24 EA $603.11 6/27/2019 PN-272679 $22.11 96 EA $2,122.19 7/4/2019 PN-272679 $25.57 24 EA $613.65 7/25/2019 PN-272679 $22.11 84 EA $1,856.92 7/25/2019 PN-272679 $25.57 24 EA $613.65 7/25/2019 PN-272679 $22.00 60 EA $1,320.20 8/14/2019 PN-272679 $25.22 24 EA $605.36 8/31/2019 PN-272679 $25.22 24 EA $605.36 8/22/2019 PN-272679 $25.22 24 EA $605.36 8/31/2019 PN-272679 $22.00 72 EA $1,584.25 8/28/2019 PN-272679 $25.22 24 EA $605.36 8/31/2019 PN-272679 $25.25 24 EA $606.06 9/20/2019 PN-272679 $25.36 24 EA $608.52 10/10/2019 PN-272679 $25.35 36 EA $912.77 10/19/2019 PN-272679 $25.36 24 EA $608.52 10/24/2019 PN-272679 $25.28 24 EA $606.63 11/5/2019 PN-272679 $22.82 48 EA $1,095.38 11/14/2019 PN-272679 $22.05 60 EA $1,322.98 11/14/2019 PN-272679 $25.28 24 EA $606.63 11/14/2019 PN-272679 $25.28 24 EA $606.63 11/29/2019 PN-272679 $21.85 150 EA $3,277.98 11/27/2019 PN-272679 $25.56 24 EA $613.44 1/2/2020 PN-272679 $25.88 24 EA $621.14 1/14/2020 PN-272679 $25.56 24 EA $613.44 1/14/2020 PN-272679 $25.88 24 EA $621.14 1/23/2020 PN-272679 $23.02 40 EA $920.80 2/6/2020 PN-272679 $23.02 40 EA $920.80 2/10/2020 PN-272679 $23.02 40 EA $920.80 2/21/2020 PN-272679 $23.02 40 EA $920.80 2/20/2020 PN-272679 $22.01 40 EA $880.33 3/2/2020 PN-272679 $21.68 40 EA $867.35 3/25/2020 PN-272679 $21.71 40 EA $868.25 4/1/2020 PN-272679 $20.74 80 EA $1,659.14 4/7/2020 PN-272679 $22.29 40 EA $891.46 5/15/2020 PN-272679 $21.51 60 EA $1,290.49 6/4/2020 PN-272679 $22.06 60 EA $1,323.37 7/6/2020 PN-272679 $22.06 81 EA $1,786.55 7/21/2020 PN-272679 $22.52 60 EA $1,351.36 8/13/2020 PN-272679 $22.83 60 EA $1,370.02 9/3/2020 PN-272679 $22.45 60 EA $1,347.10 10/9/2020