Filtering customers inactive for >90 days

[Anonymous]

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 Name	Product Received Date			Customer Name	Active/Inactive
Customer A	9/1/2021			Customer A	Inactive
Customer B	9/30/2021			Customer B	Active
Customer C	10/20/2021			Customer C	Active
Customer D	10/25/2021			Customer D	Active
Customer E	10/30/2021			Customer E	Active
Customer B	11/4/2021
Customer B	11/9/2021
Customer B	11/14/2021
Customer D	11/19/2021
Customer C	11/24/2021
Customer C	11/29/2021
Customer C	12/4/2021
Customer D	12/9/2021
Customer D	12/14/2021
Customer C	12/19/2021
Customer D	12/24/2021
Customer D	12/29/2021

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

[Ashish_Mathur]

Hi,

This measure works

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

Hope this helps.

[KNP]

Hi @Anonymous,

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"

[Anonymous]

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. 

[KNP]

You're using Power BI Desktop?

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

[Anonymous]

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. 

[KNP · 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...

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.

[Anonymous]

Thank you! Your solution worked perfectly!