Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Zosy
Helper II
Helper II

Counting New Customers based on conditions

Hi,

I am trying to find all the new customers that match the below criteria:
- they haven't bought anything in the past 5 years
- they never bought merchandise on a pallet
- they never bought merchandise in a red box

I have created the measure below but it's not returning the correct counts.

In 2017 there were 3 customers who purchased products, but only 1 is considered a new customer because the other two purchased a red box and a pallet.
In 2018 there were 0 new customers; same 2019-2022.
In 2023 there is 1 new customer.

I have uploaded the file in dropbox. Hope this helps

New Customers.pbix 

All new Customers = 
var customer = SELECTEDVALUE('Invoices'[customer name])

var LastPurchaseDate = SELECTEDVALUE('Invoices'[New Invoice Date])
            
var PreviousPurchaseDate = CALCULATE(
                MAX ( 'Date'[Date]  ), 
                FILTER(ALL('Invoices'), 'Invoices'[New Invoice Date] < LastPurchaseDate && 'Invoices'[customer name] = customer))

var PreviousPurchase = IF(ISBLANK(PreviousPurchaseDate),LastPurchaseDate,PreviousPurchaseDate)

var N = CALCULATE(DISTINCTCOUNTNOBLANK('Invoices'[customer name]),
    FILTER (
        'Invoices',
        DATEDIFF('Invoices'[New invoice Date] ,LastPurchaseDate,YEAR)>5 && 'Invoices'[Pallet] <>"Pallet" || 'Invoices'[Box Colour] <> "Red")

    )

VAR R = DISTINCTCOUNTNOBLANK('Invoices'[customer name])-N

return
N

 


Thank you

1 ACCEPTED SOLUTION

Hi,

Please try something like below.

 

All new Customers V2: =
VAR _customerlistcurrentyear =
    VALUES ( Invoices[customer name] )
VAR _customerlistpreviousfiveyears =
    CALCULATETABLE (
        VALUES ( Invoices[customer name] ),
        WINDOW ( -5, REL, -1, REL, ALL ( 'Date'[Year] ), ORDERBY ( 'Date'[Year], ASC ) ),
        REMOVEFILTERS ( 'Date' )
    )
VAR _condition =
    CALCULATETABLE (
        VALUES ( Invoices[customer name] ),
        WINDOW ( -5, REL, 0, REL, ALL ( 'Date'[Year] ), ORDERBY ( 'Date'[Year], ASC ) ),
        REMOVEFILTERS ( 'Date' ),
        'Invoices'[Pallet] = "Pallet"
            || 'Invoices'[Box Colour] = "Red"
    )
VAR _newcustomerlist =
    EXCEPT ( _customerlistcurrentyear, _customerlistpreviousfiveyears )
VAR _withoutcondition =
    EXCEPT ( _newcustomerlist, _condition )
RETURN
    COUNTROWS ( _withoutcondition )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

4 REPLIES 4
Zosy
Helper II
Helper II

Thank you @Jihwan_Kim ! Your help is very much appreciated!

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1719250507153.png

 

 

All new Customers V2: =
VAR _customerlistcurrentyear =
    VALUES ( Invoices[customer name] )
VAR _customerlistpreviousfiveyears =
    CALCULATETABLE (
        VALUES ( Invoices[customer name] ),
        WINDOW ( -5, REL, -1, REL, ALL ( 'Date'[Year] ), ORDERBY ( 'Date'[Year], ASC ) )
    )
VAR _condition =
    CALCULATETABLE (
        VALUES ( Invoices[customer name] ),
        'Invoices'[Pallet] = "Pallet"
            || 'Invoices'[Box Colour] = "Red"
    )
VAR _newcustomerlist =
    EXCEPT ( _customerlistcurrentyear, _customerlistpreviousfiveyears )
VAR _withoutcondition =
    EXCEPT ( _newcustomerlist, _condition )
RETURN
    COUNTROWS ( _withoutcondition )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Hi @Jihwan_Kim ,

The measure works great, thank you! I stumbled across another issue with it. If the user selects the year from the slicer on the page , the count is not accurate anymore? Do you know if there is a way to fix this?


Your help is very much appreciated.

Hi,

Please try something like below.

 

All new Customers V2: =
VAR _customerlistcurrentyear =
    VALUES ( Invoices[customer name] )
VAR _customerlistpreviousfiveyears =
    CALCULATETABLE (
        VALUES ( Invoices[customer name] ),
        WINDOW ( -5, REL, -1, REL, ALL ( 'Date'[Year] ), ORDERBY ( 'Date'[Year], ASC ) ),
        REMOVEFILTERS ( 'Date' )
    )
VAR _condition =
    CALCULATETABLE (
        VALUES ( Invoices[customer name] ),
        WINDOW ( -5, REL, 0, REL, ALL ( 'Date'[Year] ), ORDERBY ( 'Date'[Year], ASC ) ),
        REMOVEFILTERS ( 'Date' ),
        'Invoices'[Pallet] = "Pallet"
            || 'Invoices'[Box Colour] = "Red"
    )
VAR _newcustomerlist =
    EXCEPT ( _customerlistcurrentyear, _customerlistpreviousfiveyears )
VAR _withoutcondition =
    EXCEPT ( _newcustomerlist, _condition )
RETURN
    COUNTROWS ( _withoutcondition )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors