Customers who have not purchased Iphone 15 or certain product within 90 days with specific rules

[gbarr12345]

Hi everyone,

Little bit of a tricky query here...

I've created a Calculated table to show customers who have made purchases within the last 90 days.

This works, however I'm trying to alter this code further to include a few more rules and filter:

First, to put this code between a certain period (01/03/2024 to 31/05/2024 for example).

Second, to only include a certain product description (Iphone 15 or item code 5 only for example or any other product).

Third, to exclude STAFF from the Customer Class field in the Customer table.

Last, to only include Tesco from the Chain field in the customer table.

I know you can filter and use slicers, etc but my company are looking for set code that helps to not require slicers etc for the business users so they can see the data straight away.

Is it possible to alter my calculated table code below or even create a new calculated measure for this which would be very helpful?

I have attached my sample PBIX file too and the Excel file.

PBIX - https://drive.google.com/file/d/1c9XDUUJDHlgPzvCozeGRVgtmG_1H7QPK/view?usp=drive_link

Excel - https://docs.google.com/spreadsheets/d/1Ot-p7FnCTattQymmAPkjnq5_jclbvnRZ/edit?usp=drive_link&ouid=11...

Any help will be appreciated and please see my code below.

Many thanks in advance.

Customers with first purchase 90 Days =

FILTER(

    ADDCOLUMNS(

    ADDCOLUMNS(

    ADDCOLUMNS(

    SUMMARIZE(

        'Sales Table',

        'Sales Table'[Customer ID] ,

        "MinTransactionDate" , MIN( 'Sales Table'[Transaction Date] ),

        "MaxTransactionDate" , MAX('Sales Table'[Transaction Date] )

    ),

"1stTransactionOrNot" ,

IF( [MinTransactionDate] = [MaxTransactionDate] , "1st Transaction" , "Not the first transaction")

    ),

"DaysFromFirstTransaction" ,

IFERROR( INT( TODAY() - [MaxTransactionDate] ) , 0)

    ),

"RegularCustomerOrNot" ,

IF( [DaysFromFirstTransaction] <= 90, "Regular Customer" , "Not a regular customer") ),

AND( [RegularCustomerOrNot] = "Regular Customer" , NOT ISBLANK('Sales Table'[Customer ID] ) )

)

[v-rongtiep-msft]

Hi @gbarr12345 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _srart =
    DATE ( 2024, 3, 1 )
VAR _end =
    DATE ( 2024, 5, 31 )
VAR _itemcode =
    MAX ( 'Dimension_Item Table'[Item Code] )
RETURN
    CALCULATE (
        COUNT ( 'Dimension_Customer Table'[Customer Name] ),
        FILTER (
            ALL ( 'Sales Table' ),
            'Sales Table'[Transaction Date] >= _srart
                && 'Sales Table'[Transaction Date] <= _end
                && 'Sales Table'[Item Code] = 5
        ),
        FILTER (
            ALL ( 'Dimension_Customer Table' ),
            'Dimension_Customer Table'[Chain] = "Tesco"
                && 'Dimension_Customer Table'[Customer Class] <> "staff"
        )
    )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gbarr12345]

Hi Rongtie,

Thank you very much for your response.

I tried your measure and it's still showing users from the Staff Chain even though i want to exclude them and it's also showing all chains instead of just Tesco.

Is there a way to fix this?

[gbarr12345]

I tried making a measure for the above query and I'm getting the following error - 

No Iphone 15 90 days =

VAR FinalDate = MAX( CalendarTable[Date])

VAR StartDate = FinalDate - 90

VAR CustomersWithIphone15 =

    CALCULATETABLE(

        VALUES('Dimension_Customer Table'[Customer Name]),

        FILTER(

            'Sales Table' ,

            'Sales Table'[Item Code] = "5" &&

            'Sales Table'[Transaction Date] >= StartDate && 'Sales Table'[Transaction Date] <= FinalDate

        )

    )

RETURN

CALCULATE(

    DISTINCTCOUNT('Dimension_Customer Table'[Customer Name]) ,

    EXCEPT(

        VALUES( 'Dimension_Customer Table'[Customer Name] ) ,

        INTERSECT(

            VALUES( 'Dimension_Customer Table'[Customer Name] ),

            CustomersWithIphone15

        )

    ),

    NOT ('Dimension_Customer Table'[Customer Class] = "Domestic")

)