Visualize event that never happened vs. one that happened with Date data

[Anonymous]

Please imagine a simple table with the following data:

Customer	Last Purchased	Item
Ron	31/12/2021	Chocolates
Marie	Never (or 0)	-
Sam	12.08/2009	Apples

I want to visualize the customers who purchased most recenly and who never purchased in a singe visulization (never purchased is a negative aspect and that needs to be highlighted) - how can this be done?

One of my ideas was to put a very old date instead of never and use a bar graph, however, the idea seems, odd! 

Any ideas? Thanks!

[PaulDBrown · Accepted solution]

How about:

1) Add a date column to your table formatted as a date:

 2) create a measure to return the last purchase date by customer (or Never):

Last Purchased =
IF (
    MAX ( 'Table'[Date] ) = "Never",
    "Never",
    CALCULATE ( MAX ( 'Table'[Dates] ), ALLEXCEPT ( 'Table', 'Table'[Customer] ) )
)

3) Create a measure to use for conditional formatting:

Condit Formatting Never = IF(MAX('Table'[Date]) = "Never", 1)

Create a table visual with the customer field and the Last Purchased measure. Use the [condit formatting never] measure to format the fields in the table:

[AkshataRevankar]

Is this what you are looking for?

A DAX fucntion which converts NULL to 0 should work.

[Anonymous]

hello, thanks for your reply. Not exacty, I am not asking if I can convert Never to 0 etc., I can do it in the backend. I am rather looking for the best way to visualize, i.e. to show/highlight it to stakeholders when a date is missing (never, 0 whatever). 

[AkshataRevankar]

A table visual seems to bring up rows with no/null date and group the data based on date granularity.

[PaulDBrown]

Can you share some sample data ?

[Anonymous]

Sample Data Please note that customers can repeat.