How to dynamically select purchases based on a date slicer where the purchase is the first per user

[RogerSteinberg]

Hi,

I have a table full of Purchases per user, per date. The goal is to have a slicer where the user selects a date range and a visual (table/matrix) would display in one column the count of users who have purchased during and before that selected date range and another column for new purchasers (never purchased before that selected date range).

For example:

date	user id
1-Jan	5
2-Jan	6
3-Jan	7
4-Jan	5
5-Jan	6

If i have a date slicer that selects Jan 3r to Jan 5th, I should get this:

New Users	Old Users
1	2

That's because user id 7 never purchased before that selected range and user 5,6 have purchased before January 3rd.

What type of DAX measure can i do to achieve the new user column?

[AnkitBI]

Hi @RogerSteinberg - Check if below links are helpful

https://www.youtube.com/watch?v=GK-W25RM87Q

https://www.youtube.com/watch?v=h9kRwgamLcw

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[RogerSteinberg]

Thank you for the documentation.

I followed the video's procedure, but im getting 3 instead of 1. Nothing is being filtered. ANy idea ?

Measure:

testing_testing = 
var min_date =
    CALCULATE(
        MIN(test[date]),
        ALLSELECTED(test[date])
    )

var customers =
    values(test[user_id])

var priorcustomers = 
    CALCULATETABLE(
        VALUES(test[user_id]),
        FILTER(
            test,
            test[date] < min_date
        )
    )
    
return
COUNTROWS(
    EXCEPT(
        customers,
        priorcustomers
    )
)

[AnkitBI · Accepted solution]

Hi @RogerSteinberg Please try below. You were missing ALL.

testing_testing = 
var min_date =
    CALCULATE(
        MIN(test[date]),
        ALLSELECTED(test[date])
    )

var customers =
    values(test[user_id])

var priorcustomers = 
    CALCULATETABLE(
        VALUES(test[user_id]),
        FILTER(
        all(test),
            test[date] < min_date
        )
    )
    
return
COUNTROWS(
    EXCEPT(
        customers,
        priorcustomers
    )
)

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[RogerSteinberg]

Incredible ! Thank you .
Quick little bonus. I tried to also create a measure to calculate sales based on this measure

So i changed the following code:

return
COUNTROWS(
    EXCEPT(
        customers,
        priorcustomers
    )
)

to this but i my $ are not being filtered by my "except" filter condition:

note: my total sales measure is sumx(table,revenue)

return
CALCULATE(
    [total sales],
    EXCEPT(
        customers,
        priorcustomers
    )
)

[AnkitBI]

Hi @RogerSteinberg Try below

return
CALCULATE(
    [total sales],
    filter(test,test[user_id] in EXCEPT(
        customers,
        priorcustomers
    )
))

[RogerSteinberg]

Surprisingly, it worked when I replaced the date column from my date table with the one of my fact table

[RogerSteinberg]

It stays unfiltered