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

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: