cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danb Member
Member

Combining Volumes into table with date ranges

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

LocationItemPricePrice Start DatePrice End Date
San DiegoClips4.011/1/20193/31/2019
San DiegoClips6.14/1/20196/30/2019
San DiegoClips4.697/1/20198/25/2019
San DiegoClips6.368/26/201912/31/2019
DenverClips4.261/1/20193/31/2019
DenverClips6.654/1/20196/30/2019
DenverClips6.157/1/20198/25/2019
DenverClips6.78/26/201912/31/2019
Kansas CityClips7.511/1/20193/31/2019
Kansas CityClips5.244/1/20196/30/2019
Kansas CityClips5.467/1/20198/25/2019
Kansas CityClips6.398/26/201912/31/2019
BostonStaples7.91/1/20192/28/2019
BostonStaples6.893/1/20195/18/2019
BostonStaples7.935/19/20199/30/2019
BostonStaples7.2510/1/201912/31/2019
     

 

 

Volume Table

LocationItemSales DateQuantity
BostonStaples2/4/2019526
BostonStaples11/21/201967
DenverClips1/5/2019496
DenverClips1/18/201995
DenverClips2/25/2019330
DenverClips3/31/2019321
DenverClips5/4/2019442
DenverClips10/31/2019214
DenverClips11/25/2019417
Kansas CityClips6/22/2019451
Kansas CityClips9/11/2019506
Kansas CityClips12/25/2019252
San DiegoClips1/22/201967
San DiegoClips4/20/2019422
San DiegoClips4/30/2019443
San DiegoClips6/5/2019540
San DiegoClips8/7/2019278
San DiegoClips8/21/2019369
San DiegoClips12/12/2019157
San DiegoClips12/15/2019215

 

Here is the answer that I am expecting to have at the end:

LocationItemPricePrice Start DatePrice End DateANSWERS
San DiegoClips4.011/1/20193/31/201967
San DiegoClips6.14/1/20196/30/20191405
San DiegoClips4.697/1/20198/25/2019647
San DiegoClips6.368/26/201912/31/2019372
DenverClips4.261/1/20193/31/20191242
DenverClips6.654/1/20196/30/2019442
DenverClips6.157/1/20198/25/2019 
DenverClips6.78/26/201912/31/2019631
Kansas CityClips7.511/1/20193/31/2019 
Kansas CityClips5.244/1/20196/30/2019451
Kansas CityClips5.467/1/20198/25/2019 
Kansas CityClips6.398/26/201912/31/2019758
BostonStaples7.91/1/20192/28/2019526
BostonStaples6.893/1/20195/18/2019 
BostonStaples7.935/19/20199/30/2019 
BostonStaples7.2510/1/201912/31/201967

 

Thank you in advance! 

 

Dan

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Combining Volumes into table with date ranges

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 Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Combining Volumes into table with date ranges

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 Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

danb Member
Member

Re: Combining Volumes into table with date ranges

@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

Super User III
Super User III

Re: Combining Volumes into table with date ranges

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

Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors