Forum Discussion

RDF25087's avatar
RDF25087
Helper I
3 years ago
Solved

Date Conditional formatting

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, a...
Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    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:

     

     

  • MFelix's avatar
    MFelix
    3 years ago

    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's avatar
RDF25087
Helper I
3 years ago

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's avatar
MFelix
Super User
3 years ago

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.