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

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

 

TextTextDecimal NumberDecimal NumberDATE
CustomerProductLatitudeLongitudeLast Sale Date
Customer AProduct A57.145639-2.116231null
Customer AProduct B57.145639-2.116231null
Customer AProduct C57.145639-2.116231null
Customer BProduct A57.115432-2.07863915/06/2023
Customer BProduct B57.115432-2.07863915/06/2023
Customer BProduct C57.115432-2.07863915/06/2023
Customer BProduct D57.115432-2.07863915/06/2023
Customer CProduct A57.153437-2.14775321/04/2023
Customer CProduct B57.153437-2.14775321/04/2023
Customer CProduct C57.153437-2.14775321/04/2023
Customer DProduct A56.470032-2.96900203/01/2022
Customer DProduct B56.470032-2.96900203/01/2022
Customer DProduct C56.470032-2.96900203/01/2022
Customer DProduct D56.470032-2.96900203/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:

TextTextDecimal NumberDecimal NumberDATE 
CustomerProductLatitudeLongitudeLast Sale DateMap Point Colour
Customer AProduct A57.145639-2.116231nullBlack
Customer AProduct B57.145639-2.116231null 
Customer AProduct C57.145639-2.116231null 
Customer BProduct A57.115432-2.07863915/06/2023Green
Customer BProduct B57.115432-2.07863915/06/2023 
Customer BProduct C57.115432-2.07863915/06/2023 
Customer BProduct D57.115432-2.07863915/06/2023 
Customer CProduct A57.153437-2.14775321/04/2023Blue
Customer CProduct B57.153437-2.14775321/04/2023 
Customer CProduct C57.153437-2.14775321/04/2023 
Customer DProduct A56.470032-2.96900203/01/2022Red
Customer DProduct B56.470032-2.96900203/01/2022 
Customer DProduct C56.470032-2.96900203/01/2022 
Customer DProduct D56.470032-2.96900203/01/2022 

 

Thanks for any help.

RDF

MFelix's avatar
MFelix
Super User
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:

 

 

  • 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.