Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
frostys
Helper I
Helper I

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

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
Phil_Seamark
Employee
Employee

Hi @frostys

 

How can we tell which is a client?


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

Proud to be a Datanaut!

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

1.PNG

 

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!

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

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!

@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 

Hi @frostys

 

My formula only shows customers who have NOT make a purchase in the last three months.

 

First it detects those that have made a purchase, and then uses that list to excluded recent customers from the overall list.


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

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

Here is the output with the new formula 

 

Capture.PNG 

 

 

 

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/

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 .

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.