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, 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

 

 

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:

     

     

     

13 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    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?

  • RDF25087's avatar
    RDF25087
    Helper I
    3 years ago

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

      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?

  • 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

  • RDF25087's avatar
    RDF25087
    Helper I
    3 years ago

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

      Hi RDF25087 

       

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

  • RDF25087's avatar
    RDF25087
    Helper I
    3 years ago

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

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

    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