Max bid for each sell

[Anonymous]

Hey Community!

So I have the following problem:

I have 2 tables, one containing different bids for a product, and one containing the price, date etc. to which the product was sold.

The tables look like this:

Table bids:

Bid_id	Start_time	End_time	Product_type	price
1	18.01.2020 06:00:00	18.01.2020 06:02:33	blue	5 €
2	18.01.2020 06:00:07	18.01.2020 06:00:43	blue	7 €
3	18.01.2020 06:01:10	19.01.2020 15:03:15	red	3 €
4	18.01.2020 06:02:20	18.01.2020 06:05:44	blue	6 €

Start_time and End_time define the time, on which the bid was placed/taken down.

Table sells:

Sell_id	Sell_time	Product_type	Price
1	18.06.2020 06:00:31	blue	6,50 €
2	18:06.2020 06:51:03	red	2,50 €

The sell_id and the bid_id have no relation with each other.

What I want to find out is, what is the maximum bid to the time we sold the product (how much more expensive could we sell). So if we take sell_id 1, it should check, which bids for this specific product were active during the sell_time (in this case bid_id 1 and 2) and give back the higher price (in this case bid_id 2).

So a simple IF(start_time < sell_time AND end_time > sell_time) would suffice, however I am struggling with creating an actual measure, since those 2 tables have no relation with each other.

I hope, this somehow explains my problem. I am grateful for ANY help you can provide. Thanks in advance!

[DataInsights · Accepted solution]

@Anonymous,

Try this measure:

Max Bid = 
VAR vProduct =
    MAX ( TableSells[Product_type] )
VAR vSellTime =
    MAX ( TableSells[Sell_time] )
VAR vTableBids =
    FILTER (
        ALL ( TableBids ),
        TableBids[Product_type] = vProduct
            && vSellTime >= TableBids[Start_time]
            && vSellTime <= TableBids[End_time]
    )
VAR vResult =
    MAXX ( vTableBids, TableBids[Price] )
RETURN
    vResult

In the sample data, I assumed the Sell_time month should be "01" instead of "06".

[Anonymous]

Wow thank you for the fast answer!

Thank you very much! This works perfectly and is way faster on big databases than other solutions i have tried!

Have a nice day!

PS: Yes, i meant sell_time month "01" 🙂