Forum Discussion

RogerSteinberg's avatar
RogerSteinberg
Icon for Post Patron rankPost Patron
6 years ago
Solved

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

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:

dateuser id
1-Jan5
2-Jan6
3-Jan7
4-Jan5
5-Jan6

 

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

New UsersOld Users
12

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?

 

Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    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.

     

7 Replies

    • RogerSteinberg's avatar
      RogerSteinberg
      Icon for Post Patron rankPost Patron
      6 years ago

      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's avatar
        AnkitBI
        Icon for Solution Sage rankSolution Sage
        6 years ago

        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.