Date Conditional formatting

[RDF25087]

Hi all -

I have a column that looks at a date value and then returns:

1 if the field in the column is blank

2 if the date is less than 1 month old

3 if the date is between 1 and 3 months, and

4 if the date is older than 3 months

However, when I try to apply this to the conditional formatting it doesn't work....I am applying the following settings to the Default Color in Data Colors for the map:

What am I doing wrong?

Thanks

RDF

[MFelix · Accepted solution]

Try the following code:

Conditional Colour = 
VAR temptable =
    TOPN ( 1, 'Table', 'Table'[Last Sale Date], ASC )
VAR DateSelection =
    MAXX ( temptable, 'Table'[Last Sale Date] )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS(temptable) = 0 , BLANK(),
        DateSelection= BLANK (), "Black",
        DATEDIFF ( DateSelection, TODAY (), MONTH ) < 1, "Green",
        DATEDIFF ( DateSelection, TODAY (), MONTH ) < 3, "Light Blue",
        "Red"
    )

No use the condittional formatting from field value:

[RDF25087]

@MFelix 

Yes!! That seems to have done the trick. Thank you so much for your help and patience. Hopefully I won't bother you again!

RDF

[RDF25087]

Could it be something to do with the format of the date? I notice in your post at #6 that there isn't a calendar symbol next to Last Sale Date, whereas mine does...

[MFelix · Accepted solution]

Hi @RDF25087 ,

My bad, should be less than or equal to 1 redo the measure to:

Conditional Colour = 
VAR temptable =
    TOPN ( 1, 'Table', 'Table'[Last Sale Date], DESC)
VAR DateSelection =
    MAXX ( temptable, 'Table'[Last Sale Date] )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS(temptable) = 0 , BLANK(),
        DateSelection= BLANK (), "Black",
        DATEDIFF ( DateSelection, TODAY (), MONTH ) <= 1, "Green",
        DATEDIFF ( DateSelection, TODAY (), MONTH ) <= 3, "Blue",
        "Red"
    )

Since the calculation is in months values above 0.5 round to 1and were not considered. You may need to make some more adjustments using rounding or similar but believe this may be enough:

[RDF25087]

@MFelix 

Looking at the three points in Aberdeen.

Customer A - black - no claim - CORRECT

Customer B - last claim 15/06/2023 - blue - should be GREEN (less than 1 month)

Customer C - last claim 21/05/2023 - red - should be BLUE (between 1 and 3 months)

Thanks

RDF

[RDF25087]

Hi @MFelix 

Thanks for the response. I thought I had it, but now I can't get it to work.

I get the following:

Using the following measure for the conditional formatting:

Thanks

RDF

[MFelix]

Hi @RDF25087 

What is incorrect in the image? Can you please tell me? it may need some ajdustment on the metric.

[RDF25087]

This is how my table data looks. I've added the field format at the top:

Text	Text	Decimal Number	Decimal Number	DATE
Customer	Product	Latitude	Longitude	Last Sale Date
Customer A	Product A	57.145639	-2.116231	null
Customer A	Product B	57.145639	-2.116231	null
Customer A	Product C	57.145639	-2.116231	null
Customer B	Product A	57.115432	-2.078639	15/06/2023
Customer B	Product B	57.115432	-2.078639	15/06/2023
Customer B	Product C	57.115432	-2.078639	15/06/2023
Customer B	Product D	57.115432	-2.078639	15/06/2023
Customer C	Product A	57.153437	-2.147753	21/04/2023
Customer C	Product B	57.153437	-2.147753	21/04/2023
Customer C	Product C	57.153437	-2.147753	21/04/2023
Customer D	Product A	56.470032	-2.969002	03/01/2022
Customer D	Product B	56.470032	-2.969002	03/01/2022
Customer D	Product C	56.470032	-2.969002	03/01/2022
Customer D	Product D	56.470032	-2.969002	03/01/2022

The customer appears multiple times because of the different products. The Last Sale date is just the last date a sale was made to that customer - not for the specific product.

When plotted on a map, the points should be coloured as follows:

Text	Text	Decimal Number	Decimal Number	DATE
Customer	Product	Latitude	Longitude	Last Sale Date	Map Point Colour
Customer A	Product A	57.145639	-2.116231	null	Black
Customer A	Product B	57.145639	-2.116231	null
Customer A	Product C	57.145639	-2.116231	null
Customer B	Product A	57.115432	-2.078639	15/06/2023	Green
Customer B	Product B	57.115432	-2.078639	15/06/2023
Customer B	Product C	57.115432	-2.078639	15/06/2023
Customer B	Product D	57.115432	-2.078639	15/06/2023
Customer C	Product A	57.153437	-2.147753	21/04/2023	Blue
Customer C	Product B	57.153437	-2.147753	21/04/2023
Customer C	Product C	57.153437	-2.147753	21/04/2023
Customer D	Product A	56.470032	-2.969002	03/01/2022	Red
Customer D	Product B	56.470032	-2.969002	03/01/2022
Customer D	Product C	56.470032	-2.969002	03/01/2022
Customer D	Product D	56.470032	-2.969002	03/01/2022

Thanks for any help.

RDF

[MFelix · Accepted solution]

Try the following code:

Conditional Colour = 
VAR temptable =
    TOPN ( 1, 'Table', 'Table'[Last Sale Date], ASC )
VAR DateSelection =
    MAXX ( temptable, 'Table'[Last Sale Date] )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS(temptable) = 0 , BLANK(),
        DateSelection= BLANK (), "Black",
        DATEDIFF ( DateSelection, TODAY (), MONTH ) < 1, "Green",
        DATEDIFF ( DateSelection, TODAY (), MONTH ) < 3, "Light Blue",
        "Red"
    )

No use the condittional formatting from field value:

[RDF25087]

@MFelix- you Sir, are a legend. Thank you so much for your support with this issue. I couldn't have resolved this on my own.

For my own learning, could you give me an explanation of what your DAX formula does?

Thank you

RDF

[MFelix]

Hi @RDF25087 ,

First of all there is an error on the formula you need to do a descending and not ascending so that you pick up the latest date and not the first:

Conditional Colour = 
VAR temptable =
    TOPN ( 1, 'Table', 'Table'[Last Sale Date], ASC )
VAR DateSelection =
    MAXX ( temptable, 'Table'[Last Sale Date] )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS(temptable) = 0 , BLANK(),
        DateSelection= BLANK (), "Black",
        DATEDIFF ( DateSelection, TODAY (), MONTH ) < 1, "Green",
        DATEDIFF ( DateSelection, TODAY (), MONTH ) < 3, "Light Blue",
        "Red"
    )

This is the concepts behind the formula:

TOPN ( 1, 'Table', 'Table'[Last Sale Date], ASC ) - Pick up the first row of data from your table so for customer B it would return

Customer B

Product A

57.115432

-2.078639

15/06/2023

MAXX ( temptable, 'Table'[Last Sale Date] ) - Select the date in this case 15/06/2023

SWITCH - Make the calculation for the colours using a difference between the previous date and today.

[RDF25087]

I want to show the user a map with their customers plotted. I have the longitude and latitude. However, I wanted the user to quickly see how recently a customer has bought - black if never bought (1), green for less than a month ago (2), blue for between 1 and 3 months ago (3) and red for longer than 3 months ago (4)

[MFelix]

Hi @RDF25087 ,

Believe that for this you need to have a measure that makes that calculation and then apply the condittional formatting to that metric.

How does your data looks like?

[MFelix]

Hi @RDF25087 ,

The question is that you are using the SUM so your values do not match with 1, 2, 3, 4.

How are you using this column on your visualization?