Combining Volumes into table with date ranges

[danb]

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

[MFelix · Accepted 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

[danb]

@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

[MFelix]

Hi @danb,

The use of comma or dot comma is related with the regional settings not with the version of PBI, is the same as decimal mark that in some countries is the comma and on other the dot.


Regards,
MFelix