Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I'm looking to get help with a formula that will get me the weighted average based off a set of paramters. I would like the formula to take the sum of spend per material number and divide it by the sum of quantites for that material number. I would like it to do this based off the most recent purchase though and have it look 365 days backwards. So if I make a purchase today on material 12345 then the formula will take today and the last 365 days of purchases for that material and give me the weighted average based off that date range.
Secondly, if there could be a formula that brings me back the most recent purchase price. So the formula goes out and looks for the most recent time we purchased that material and brings back that price.
Any help with this would be great
Thank you!
Solved! Go to Solution.
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)
Proud to give back to the community!
Thank You!
Try this....
Proud to give back to the community!
Thank You!
Please consider posting some sample mock data, with desired result. Particaully, more information on how you want the values weighted based on a date range. It doesn't sound horribly difficult, but it would help to know how your data is structured before trying to provide you code samples...
Forrest
Proud to give back to the community!
Thank You!
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
Since you will want the Last Purchase Date, and we'll use it minus 1 year in the CAL formula, start with that as it's own measure:
Proud to give back to the community!
Thank You!
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)
Proud to give back to the community!
Thank You!
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 =
Try this....
Proud to give back to the community!
Thank You!
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!
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 |
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
Proud to give back to the community!
Thank You!
Is you Date column in the original data formatted to be a Date Value in Power BI?
FOrrest
Proud to give back to the community!
Thank You!
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...
Proud to give back to the community!
Thank You!
Hello,
Yes it is.
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
54 |