Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filtering clients Need help!

Hello everyone!) I need to filter clients in three ways: 1) Filtering customers without purchases within X days (X means that instead of X there should be a "what if" parameter that will change) 2)...
Need Help
Show and Tell
Tutorial Requests
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi Anonymous 

For 

2) Filtering customers with at least 1 purchase per week
3) Filtering customers with at least 1 purchase per month

 

I create a date table

Date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"week",WEEKNUM([Date],2))

Create a measure in "Date" table

count per month/week = COUNT('Documents(Purchases)'[DocumentDiscountId])

 

But for 

1) Filtering customers without purchases within X days (X means that instead of X there should be a "what if" parameter that will change)

Do you mean last X days before today?

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi, v-juanli-msft! Hello! Thank you for answering!) Unfortunately, I need the quantity per month and the quantity per week to be calculated separately (in different columns)

Now about X. X - is the number of days. It means that I can substitute any number instead of X and find out the number of customers who had no purchases X days ago. Yes, it means last X days before today. 

And one more small question. Can I somehow calculate the number of clients per month and per week without using a calendar?

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    First we do need a calendar table as created in my previous reply.

     

    Second, "quantity per month and the quantity per week " as below

    I create two measures (it will calculate  more quickly then columns do)

     

    per month = CALCULATE( COUNT('Documents(Purchases)'[DocumentDiscountId]),ALLEXCEPT('Date','Date'[year],'Date'[month]))

count per week = CALCULATE(COUNT('Documents(Purchases)'[DocumentDiscountId]),ALLEXCEPT('Date','Date'[year],'Date'[week]))

     

    Fianlly, find out the number of customers who had no purchases X days ago. Yes, it means last X days before today. 

     

    1.

    Create a what-if parameter

    (thus, i get a table called "last days", a column "last days" and add it in a slicer

    last days = GENERATESERIES(0, 30, 1)

    2.

    Create measures

    last n days =
DATEDIFF ( MAX ( 'Documents(Purchases)'[DocumentsDateTime] ), TODAY (), DAY ) < 0
    || DATEDIFF ( MAX ( 'Documents(Purchases)'[DocumentsDateTime] ), TODAY (), DAY )
        <= SELECTEDVALUE ( 'last days'[last days] )


pur in x days =
IF (
    NOT (
        ISBLANK (
            CALCULATE (
                DISTINCTCOUNT ( Clients[ClientId] ),
                FILTER ( Clients, [last n days] = TRUE () )
            )
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( Clients[ClientId] ),
        FILTER ( Clients, [last n days] = TRUE () )
    )
)

final count = CALCULATE(DISTINCTCOUNT(Clients[ClientId]),ALL(Clients))-[pur in x days]

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello, v-juanli-msft!!
      1. About Filtering clients, who haven't purchases X days ago. It's works great! Thank you so much!
      2. About Filtering per month/per week. I think there are errors in the calculations. The fact is that if we compare the purchase dates of all customers, we will see that at least once a month only customers with clientId = 1, 2, 3 bought. And at least once a week bought only clientId = 4 and clientId =5.


      Finally, I need to have only three clients in table "per month", and only two clients in table "per week".

      Thank you for your patience!