cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahabelow13
New Member

Count a sale for a customer only outside of 15 days from the first sale, and any subsequent sales

My dashboard for customer sales has many "duplicates". For example:

 

pbi.png

 

Customer Bryan Smith has made 7 purchases in the past year total. For my specific dashboard, I want to filter out the cells that are not highlighted in green (rows 3, 7, and 😎 because they are within 15 days of a previous purchase. I have a Customer Key (CusKey) as a unique identifier for my customers.

 

The calculated column I created is:

 

dupeTest =
VAR MaxDateKey = 'Sales'[DateOfPurchase]
VAR MinDateKey = maxdatekey - 15
VAR Calc =
CALCULATE (
COUNTROWS ( 'Sales' ),
FILTER (
'Sales',
'Sales'[DateOfPurchase] >= MinDateKey
&& 'Sales'[DateOfPurchase] <= MaxDateKey
&& 'Sales'[CusKey] = CusKey
)
)
RETURN
IF ( calc > 1, 0, 1 )
 
And then to get my desired result, I filtered out all 0's in that column so those entries wouldn't be in my data.
 
The data I'm trying to filter out is still present. In this specific example with my formula, row 3 would be erased because it is within 15 days of row 1's date (this is what I want). Row 7 and 8 still remain although they are 15 days within row 6. My desired results after implementing that formula is as follows:
pbi2.png
 
Help would be greatly appreciated!
1 REPLY 1
lbendlin
Super User
Super User

IF ( calc > 0, 0, 1 )

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors