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) Filtering customers with at least 1 purchase per week
3) Filtering customers with at least 1 purchase per month

Thanks in advance!!

It is a link to Power Bi file, where is a example of my DataBase - https://www.dropbox.com/s/09kcljnmx6zhuhw/Example.pbix?dl=0

And pic of my tables:
Accounts:
 

 

Clients:

 

Discounts:

 

Purchases:

Need Help
Show and Tell
Tutorial Requests

6 Replies

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

    Hi Anonymous 

    Anonymous wrote:

    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.

    Sorry, i don't understand the requirement, to get solutions for this, you may need post a new one. Others may help you.

     

    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

      v-juanli-msft  Ok, possibly I inaccurately stated the requirements. I need to filter out customers who have made a purchase at least once a month. For example, our date slicer captures three months: June, July and August. If the customer made at least one purchase in each of the months, he will pass through the filter. If this customer does not make a purchase in at least one of the months, the filter will reject it. Same thing with weeks.

  • 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.