cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnGray
Helper I
Helper I

Filtering customers inactive for >90 days

Hello,

 

I am looking to create a calculated column that will categorize my customer as "Active" or "Inactive" based on whether they have sent us anything in the past 90 days. I would like a formula that would look up the most recent "Received Date" for a customer and then be able to classify the customer as a whole as either "Active" or Inactive". 

Data and output woud look something like this:

 

Customer NameProduct Received Date  Customer NameActive/Inactive
Customer A9/1/2021  Customer AInactive
Customer B9/30/2021  Customer BActive
Customer C10/20/2021  Customer CActive
Customer D10/25/2021  Customer DActive
Customer E10/30/2021  Customer EActive
Customer B11/4/2021    
Customer B11/9/2021    
Customer B11/14/2021    
Customer D11/19/2021    
Customer C11/24/2021    
Customer C11/29/2021    
Customer C12/4/2021    
Customer D12/9/2021    
Customer D12/14/2021    
Customer C12/19/2021    
Customer D12/24/2021    
Customer D12/29/2021    

 

Is this something that can be done using calculated columns? Thank you!

1 ACCEPTED SOLUTION

Ok, that makes more sense.

You could maybe try creating a new table (DAX) to track the customer status.

I haven't completely tested it, but something like this...

KNP_0-1642624641126.png

 

Customer Status = 
SUMMARIZECOLUMNS (
    'Table'[Customer Name],
    "MaxDate", MAX ( 'Table'[Product Received Date] ),
    "Days Since Active",
        DATEDIFF (
            MAX ( 'Table'[Product Received Date] ),
            TODAY (),
            DAY
        ),
    "A/I",
        IF (
            DATEDIFF (
                MAX ( 'Table'[Product Received Date] ),
                TODAY (),
                DAY
            ) < 90,
            "Active",
            "Inactive"
        )
)

 

Then make sure the relationships are setup in the model.

KNP_1-1642624751127.png

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

This measure works

Measure = if(today()>=(MAX(Data[Product Received Date])+90),"Inactive","Active")

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
KNP
Super User
Super User

Hi @JohnGray,

 

I prefer to do my data modelling in Power Query. I believe that is the more appropriate place for it.

If you're happy to do it there instead of using calculated columns, please see attached PBIX.

 

Here's the code that defines the status.

let
  Source = Table,
  #"Grouped Rows" = Table.Group(
    Source,
    {"Customer Name"},
    {{"MaxDate", each List.Max([Product Received Date]), type nullable date}}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Grouped Rows",
    "Active/Inactive",
    each
      if Duration.TotalDays(DateTime.Date(DateTime.FixedLocalNow()) - [MaxDate]) < 90 then
        "Active"
      else
        "Inactive"
  ),
  #"Changed Type" = Table.TransformColumnTypes(#"Added Custom", {{"Active/Inactive", type text}})
in
  #"Changed Type"

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Hello KNP,

 

Thank you for your help. Unfortunately, the file sources I am using for PowerBI are for our whole company and I do not have the credentials to access the data source, so I cannot use Power Query. Everything I do in this database must be done through calculated columns or measures. 

You're using Power BI Desktop?

By Power Query, I'm referring to the 'Transform Data', or am I still misunderstanding something?

KNP_0-1642618004558.png

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Hi KNP,

 

Yes, I am using desktop, but when I click on the "Transform Data" option, I need special credentials to log into the SQL Server database, where this data is all pulling from. I unfortunately do not have the necessary credentials/permissions to modify this data via the Transform Data option. 

Ok, that makes more sense.

You could maybe try creating a new table (DAX) to track the customer status.

I haven't completely tested it, but something like this...

KNP_0-1642624641126.png

 

Customer Status = 
SUMMARIZECOLUMNS (
    'Table'[Customer Name],
    "MaxDate", MAX ( 'Table'[Product Received Date] ),
    "Days Since Active",
        DATEDIFF (
            MAX ( 'Table'[Product Received Date] ),
            TODAY (),
            DAY
        ),
    "A/I",
        IF (
            DATEDIFF (
                MAX ( 'Table'[Product Received Date] ),
                TODAY (),
                DAY
            ) < 90,
            "Active",
            "Inactive"
        )
)

 

Then make sure the relationships are setup in the model.

KNP_1-1642624751127.png

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Thank you! Your solution worked perfectly!

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!