cancel
Showing results for
Did you mean:
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 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!

1 ACCEPTED SOLUTION
Super User

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.

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

 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. Proud to be a Super User!
7 REPLIES 7
Super User

Hi,

This measure works

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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}}
),
#"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 ;).

 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. Proud to be a Super User!
Helper I

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.

Super User

You're using Power BI Desktop?

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

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

 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. Proud to be a Super User!
Helper I

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.

Super User

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.

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

 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. Proud to be a Super User!
Helper I

Thank you! Your solution worked perfectly!

Announcements

#### The Power BI Community Show

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