cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
frostys Regular Visitor
Regular Visitor

Clients who haven't sent orders for more than 3 months

Hi !

 

I try to make a DAX function to show clients who have no sent any orders for more than 3 months.

 

With this kind of data :

 

1.PNG

 

Thank you for your help 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: Clients who haven't sent orders for more than 3 months

HI @frostys

 

Please try this tweak

 

Table = 
VAR CustomersPurchasedLast3Months = 
        SELECTCOLUMNS(
            FILTER(
                'Table2',
                EOMONTH('Table2'[Date],3)>TODAY()
                ),
            "Customer",[Controlling Customer Name])

RETURN EXCEPT(
            ALL('Table2'[Controlling Customer Name]),
            CustomersPurchasedLast3Months
            )   

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

10 REPLIES 10
Microsoft Phil_Seamark
Microsoft

Re: Clients who haven't sent orders for more than 3 months

Hi @frostys

 

How can we tell which is a client?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

frostys Regular Visitor
Regular Visitor

Re: Clients who haven't sent orders for more than 3 months

The order number is related to a clients name and client code, I forget to mention that sorry !

1.PNG

 

Super User IV
Super User IV

Re: Clients who haven't sent orders for more than 3 months

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Microsoft Phil_Seamark
Microsoft

Re: Clients who haven't sent orders for more than 3 months

Hi @frostys

 

This calculated table will give you a list of your customers

 

Table = 
VAR CustomersPurchasedLast3Months = 
        SELECTCOLUMNS(
            FILTER(
                'Table2',
                EOMONTH('Table2'[Date],3)>TODAY()
                ),
            "Customer",[Controlling Customer Name])

RETURN EXCEPT(
            VALUES('Table2'[Controlling Customer Name]),
            CustomersPurchasedLast3Months
            )   

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

frostys Regular Visitor
Regular Visitor

Re: Clients who haven't sent orders for more than 3 months

Hi Ashish_Mathur, 

 

I can not share this data.

Perhaps you know a dax function to detect the nonoccurence (of custumer) in a given time .

frostys Regular Visitor
Regular Visitor

Re: Clients who haven't sent orders for more than 3 months

Thank you for you respond ! @Phil_Seamark

 

I created this formula but I try to figure out why nothing output when I make the visualization 

 

Capture.PNG

Microsoft Phil_Seamark
Microsoft

Re: Clients who haven't sent orders for more than 3 months

HI @frostys

 

Please try this tweak

 

Table = 
VAR CustomersPurchasedLast3Months = 
        SELECTCOLUMNS(
            FILTER(
                'Table2',
                EOMONTH('Table2'[Date],3)>TODAY()
                ),
            "Customer",[Controlling Customer Name])

RETURN EXCEPT(
            ALL('Table2'[Controlling Customer Name]),
            CustomersPurchasedLast3Months
            )   

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

frostys Regular Visitor
Regular Visitor

Re: Clients who haven't sent orders for more than 3 months

Hi @Phil_Seamark,

 

Here is the output with the new formula 

 

Capture.PNG 

 

 

 

frostys Regular Visitor
Regular Visitor

Re: Clients who haven't sent orders for more than 3 months

@Phil_Seamark, But with this solution, it will just show the customers of the last 3 months ?

 

I was more thinking about a dax formula who output the customer who have not orders anything from the last 3 months 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors