Advanced measure - Customers from Canada who haven't purchased a certain product in March to May

[gbarr12345]

Hi everyone,

I'm trying to create a measure that filters only on Users from Canada who haven't purchased the item "CA6001" between the months of March 2024 and May 2024.

The tables included are as follows:

    Dimension_period which would have the FYperiod such as 202403 for March and 202405 for May

    Dimension_Market which would have the regions like Canada, etc.

    Dimension_Customer which would have the customer name.

    Dimension_item which would have the item code such as "CA6001".

I have attempted a measure below but to no avail. I have it pasted below for your reference.

All the relationships are correct too as poer my screenshot.

If anyone could show me a correct measure that would work instead and maybe tell me the best way to input it into a visualisation that would be greatly appreciated 🙂

Thank you in advance!

Canada cust who haven't bought 19AU1000 =

VAR AllCanadaCustomers =

    CALCULATETABLE (

        VALUES ( 'Dimension Customer'[description] ),

        'Dimension Market'[market] = "CAN"

    )

VAR PurchasedCA6001Customers =

    CALCULATETABLE (

        VALUES ( 'Dimension Customer'[description] ),

        'Dimension Item'[item] = "CA6001",

        'Dimension Period'[fyperiod] >= DATE ( 2024, 3, 1 ) &&

        'Dimension Period'[fyperiod] <= DATE ( 2024, 5, 31 )

    )

VAR CustomersWhoBoughtCA6001InPeriod =

    INTERSECT(AllCanadaCustomers, PurchasedCA6001Customers)

VAR CustomersWhoBoughtCA6001 =

    CALCULATETABLE (

        VALUES ( 'Dimension Customer'[description] ),

        'Dimension Item'[item] = "CA6001"

    )

VAR CustomersWhoDidNotBuyCA6001 =

    EXCEPT(AllCanadaCustomers, CustomersWhoBoughtCA6001)

RETURN

    EXCEPT(CustomersWhoDidNotBuyCA6001, CustomersWhoBoughtCA6001InPeriod)

[AndyEagleton]

The first argument to SUMMARIZE is a table. The amount is irrelevant (assuming the presence of a record in the fact table indicates a sale took place).

[gbarr12345]

The Amount is showing as red error also.

I'm wondering is there a workaround code or something to fix this?

All the tables in my data model have correct relationships also.

[AndyEagleton]

Yes some kind of fact table is needed to identify whether a customer purchased the item in the time range (otherwise you don't have the information). Normally this would be some kind of sales table with keys to the customer dimension, date dimension and item dimension.

[gbarr12345]

When I input the table that has the sales figure I get a red error.

What's the best way to fix this?

Apologies I'm new to Power BI so unsure of some things.

[AndyEagleton]

Try this:

VAR Months = TREATAS({202403, 202404, 202405}, Dimension_period[FYperiod])

VAR Product = TREATAS({"CA6001"}, Dimension_item[item])

VAR Canada = TREATAS({"CAN"}, Dimension_market[market])

VAR CanadianCustomersWhoBought = 

    CALCULATETABLE(

        SUMMARIZE(Sales, Dimension_customer[description]),

        Months, Product, Canada, ALL()

    )

VAR AllCanadianCustomers = 

    CALCULATETABLE(

        SUMMARIZE(Sales, Dimension_customer[description]),

        Canada, ALL()

    )

VAR CanadianCustomersWhoDidNotBuy = EXCEPT(AllCanadianCustomers, CanadianCustomersWhoBought)

VAR CurrentCustomer = SELECTEDVALUE(Dimension_customer[description])

RETURN 

    SWITCH(

        TRUE(),

        CurrentCustomer IN CanadianCustomersWhoBought, "Canadian Customer Who Bought",

        CurrentCustomer IN CanadianCustomersWhoDidNotBuy, "Canadian Customer Who Did Not Buy",

        "Non Canadian Customer"

    )

[gbarr12345]

Thanks for the response.

I haven't got a Sales table in my data. I have a table called 'Module Sales with Inventory' that has an amount field.

Do I need to put something else instead of sales?

[gbarr12345]

I have included below sample data and the expected outcome also below here if that would help: