Track Lowest Priced Items Over Time

[b2wise]

Hi all,

I have an item header table and a price history table along with a date table. I am trying to count distinct items in which Daisy (my company) had the lowest price vs the competition (Denways and Carolina Dreaming) and show that over time.

These are my tables:

Item Headers

Item	Description
6	Seeds
7	Flowers
8	Soil

Price History

Date	Item	Vendor	Qty Break	Price
5/8/2022	8	Daisy	1	355.00
5/8/2022	8	Carolina Dreaming	1	315.00
5/8/2022	8	Denways	1	328.00
5/8/2022	6	Daisy	1	228.00
5/8/2022	6	Carolina Dreaming	1	389.00
5/8/2022	6	Denways	1	107.00
5/8/2022	7	Daisy	1	97.00
5/8/2022	7	Carolina Dreaming	1	347.00
5/8/2022	7	Denways	1	120.00
5/9/2022	7	Daisy	1	377.00
5/9/2022	7	Carolina Dreaming	1	223.00
5/9/2022	7	Denways	1	187.00
5/9/2022	6	Daisy	1	337.00
5/9/2022	6	Carolina Dreaming	1	255.00
5/9/2022	6	Denways	1	218.00
5/9/2022	8	Daisy	1	199.00
5/9/2022	8	Carolina Dreaming	1	371.00
5/9/2022	8	Denways	1	356.00
5/10/2022	6	Daisy	1	366.00
5/10/2022	6	Carolina Dreaming	1	299.00
5/10/2022	6	Denways	1	81.00
5/10/2022	7	Daisy	1	123.00
5/10/2022	7	Carolina Dreaming	1	5.00
5/10/2022	7	Denways	1	63.00
5/10/2022	8	Daisy	1	229.00
5/10/2022	8	Carolina Dreaming	1	119.00
5/10/2022	8	Denways	1	258.00

This is the result I am trying to get:

Date	Daisy Items Listed	Daisy Winning Items
5/8/2022	3	0
5/9/2022	3	0
5/10/2022	3	1

Looks simple but I'm not sure how to do this in DAX.

Thanks in advance!

[DataInsights · Accepted solution]

@b2wise,

Try these measures. In the measure Daisy Winning Items, I get different results from your expected result. In the sample data for 5/10/2022, Daisy doesn't have the lowest price for any Item but your expected result shows 1.

Daisy Items Listed =
CALCULATE (
    DISTINCTCOUNT ( 'Price History'[Item] ),
    'Price History'[Vendor] = "Daisy"
)

Daisy Winning Items = 
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Price History',
            DimDate[Date],
            'Price History'[Item],
            'Price History'[Vendor],
            'Price History'[Price]
        ),
        // calculate the min price for each combination of Date and Item
        "@MinPrice",
            CALCULATE (
                MIN ( 'Price History'[Price] ),
                ALLEXCEPT ( 'Price History', DimDate[Date], 'Price History'[Item] )
            )
    )
VAR vResult =
    SUMX (
        FILTER ( vTable, 'Price History'[Vendor] = "Daisy" ),
        IF ( 'Price History'[Price] = [@MinPrice], 1 )
    )
RETURN
    vResult