Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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! |
Hi,
This measure works
Measure = if(today()>=(MAX(Data[Product Received Date])+90),"Inactive","Active")
Hope this helps.
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 ;). |
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! |
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?
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! |
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...
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! |
Thank you! Your solution worked perfectly!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |