Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Afternoon experts!
I am stumped. I have two tables, one that has pricing for different items by location and date range. The second table has the sales numbers by date / location. I would like to combine the volumes of sales into my pricing table and have it reflect how many items were sold for that pricing range.
Pricing Table
Location | Item | Price | Price Start Date | Price End Date |
San Diego | Clips | 4.01 | 1/1/2019 | 3/31/2019 |
San Diego | Clips | 6.1 | 4/1/2019 | 6/30/2019 |
San Diego | Clips | 4.69 | 7/1/2019 | 8/25/2019 |
San Diego | Clips | 6.36 | 8/26/2019 | 12/31/2019 |
Denver | Clips | 4.26 | 1/1/2019 | 3/31/2019 |
Denver | Clips | 6.65 | 4/1/2019 | 6/30/2019 |
Denver | Clips | 6.15 | 7/1/2019 | 8/25/2019 |
Denver | Clips | 6.7 | 8/26/2019 | 12/31/2019 |
Kansas City | Clips | 7.51 | 1/1/2019 | 3/31/2019 |
Kansas City | Clips | 5.24 | 4/1/2019 | 6/30/2019 |
Kansas City | Clips | 5.46 | 7/1/2019 | 8/25/2019 |
Kansas City | Clips | 6.39 | 8/26/2019 | 12/31/2019 |
Boston | Staples | 7.9 | 1/1/2019 | 2/28/2019 |
Boston | Staples | 6.89 | 3/1/2019 | 5/18/2019 |
Boston | Staples | 7.93 | 5/19/2019 | 9/30/2019 |
Boston | Staples | 7.25 | 10/1/2019 | 12/31/2019 |
Volume Table
Location | Item | Sales Date | Quantity |
Boston | Staples | 2/4/2019 | 526 |
Boston | Staples | 11/21/2019 | 67 |
Denver | Clips | 1/5/2019 | 496 |
Denver | Clips | 1/18/2019 | 95 |
Denver | Clips | 2/25/2019 | 330 |
Denver | Clips | 3/31/2019 | 321 |
Denver | Clips | 5/4/2019 | 442 |
Denver | Clips | 10/31/2019 | 214 |
Denver | Clips | 11/25/2019 | 417 |
Kansas City | Clips | 6/22/2019 | 451 |
Kansas City | Clips | 9/11/2019 | 506 |
Kansas City | Clips | 12/25/2019 | 252 |
San Diego | Clips | 1/22/2019 | 67 |
San Diego | Clips | 4/20/2019 | 422 |
San Diego | Clips | 4/30/2019 | 443 |
San Diego | Clips | 6/5/2019 | 540 |
San Diego | Clips | 8/7/2019 | 278 |
San Diego | Clips | 8/21/2019 | 369 |
San Diego | Clips | 12/12/2019 | 157 |
San Diego | Clips | 12/15/2019 | 215 |
Here is the answer that I am expecting to have at the end:
Location | Item | Price | Price Start Date | Price End Date | ANSWERS |
San Diego | Clips | 4.01 | 1/1/2019 | 3/31/2019 | 67 |
San Diego | Clips | 6.1 | 4/1/2019 | 6/30/2019 | 1405 |
San Diego | Clips | 4.69 | 7/1/2019 | 8/25/2019 | 647 |
San Diego | Clips | 6.36 | 8/26/2019 | 12/31/2019 | 372 |
Denver | Clips | 4.26 | 1/1/2019 | 3/31/2019 | 1242 |
Denver | Clips | 6.65 | 4/1/2019 | 6/30/2019 | 442 |
Denver | Clips | 6.15 | 7/1/2019 | 8/25/2019 | |
Denver | Clips | 6.7 | 8/26/2019 | 12/31/2019 | 631 |
Kansas City | Clips | 7.51 | 1/1/2019 | 3/31/2019 | |
Kansas City | Clips | 5.24 | 4/1/2019 | 6/30/2019 | 451 |
Kansas City | Clips | 5.46 | 7/1/2019 | 8/25/2019 | |
Kansas City | Clips | 6.39 | 8/26/2019 | 12/31/2019 | 758 |
Boston | Staples | 7.9 | 1/1/2019 | 2/28/2019 | 526 |
Boston | Staples | 6.89 | 3/1/2019 | 5/18/2019 | |
Boston | Staples | 7.93 | 5/19/2019 | 9/30/2019 | |
Boston | Staples | 7.25 | 10/1/2019 | 12/31/2019 | 67 |
Thank you in advance!
Dan
Solved! Go to Solution.
Hi @danb ,
You can add a measure or a column depending on your setup check formulas below:
Measure = CALCULATE ( SUM ( Volume[Quantity] ); FILTER ( ALL ( Volume[Item]; Volume[Location]; Volume[Quantity]; Volume[Sales Date] ); MAX ( Pricing[Item] ) = Volume[Item] && MAX ( Pricing[Location] ) = Volume[Location] && Volume[Sales Date] >= MAX ( Pricing[Price Start Date] ) && Volume[Sales Date] <= MAX ( Pricing[Price End Date] ) ) ) + 0 Column = CALCULATE ( SUM ( Volume[Quantity] ); FILTER ( ALL ( Volume[Item]; Volume[Location]; Volume[Quantity]; Volume[Sales Date] ); Pricing[Item] = Volume[Item] && Pricing[Location] = Volume[Location] && Volume[Sales Date] >= Pricing[Price Start Date] && Volume[Sales Date] <= Pricing[Price End Date] ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @danb ,
You can add a measure or a column depending on your setup check formulas below:
Measure = CALCULATE ( SUM ( Volume[Quantity] ); FILTER ( ALL ( Volume[Item]; Volume[Location]; Volume[Quantity]; Volume[Sales Date] ); MAX ( Pricing[Item] ) = Volume[Item] && MAX ( Pricing[Location] ) = Volume[Location] && Volume[Sales Date] >= MAX ( Pricing[Price Start Date] ) && Volume[Sales Date] <= MAX ( Pricing[Price End Date] ) ) ) + 0 Column = CALCULATE ( SUM ( Volume[Quantity] ); FILTER ( ALL ( Volume[Item]; Volume[Location]; Volume[Quantity]; Volume[Sales Date] ); Pricing[Item] = Volume[Item] && Pricing[Location] = Volume[Location] && Volume[Sales Date] >= Pricing[Price Start Date] && Volume[Sales Date] <= Pricing[Price End Date] ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Thank you! That worked great. I am going to use the measure as it is a larger dataset. The only thing I had to adjust was replacing the semi-colons with commas. Not sure if I have an old version of Power BI or why I had to do that.
Anyways, many thanks again!
Dan
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |