cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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:
 Accounts.jpg

 

Clients:

Clients.jpg

 

Discounts:

Discounts.jpg

 

Purchases:

Purchases.jpg

6 REPLIES 6
Highlighted
Community Support
Community Support

Hi @EgorS 

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))

Capture17.JPG

Create a measure in "Date" table

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

Capture16.JPG

 

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.

Highlighted

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?

Highlighted

Hi @EgorS 

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]))

Capture7.JPG

 

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]

Capture8.JPG

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.

Highlighted

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!

Highlighted
Community Support
Community Support

Hi @EgorS 


@EgorS 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.

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors