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!