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
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
Solved! Go to 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.
Hi,
Please check the below picture and the attached pbix file.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
14 | |
12 | |
10 | |
10 |