Forum Discussion

bman6074's avatar
bman6074
Helper I
7 years ago
Solved

Conditional color formatting for date range

I would like to change the color according to a date range. and where table stats Preferred for model.    Mark AS Red when EOSL Date is not NULL and EOSL Date is within 1 year ahead of the current ...
Need Help
  • bman6074's avatar
    bman6074
    7 years ago

    I figured it out. Using this DAX script 

     

    EndofSupport Colors3 =
    VAR todaysd = TODAY ()
    VAR d = MAX ( 'NCM_NodeProperties'[EndOfSupport] )
    VAR dated = DATEDIFF ( todaysd, d, DAY )
    VAR RoadMap = FALSE()
    VAR eol = MAX ( 'NCM_NodeProperties'[EndOfSoftware] )
    VAR eolDiff = DATEDIFF( todaysd, eol, DAY )
    RETURN
    IF ( ISBLANK ( d ), BLANK (), IF ( RoadMap, "#7BBA00",
    IF ( dated <= 365, "#E01920",
    IF ( dated <= 730 && dated > 365, "#FAB131",
    IF ( dated > 730 && eolDiff < 0, "#FF5A00", "#7BBA00"

    // EOSL is within 1 year ahead of the current date, "#E01920", RED
    // EOSL is between 1 and 2 years away "#FAB131", Yellow
    //EOL is in the past, and EOSL is more than 2 years away "#FF5A00", Orange
    //"#7BBA00" Green
    )))))
v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi bman6074 ,

 

One sample for your reference. We can create a meausre as below and make the column formated by it.

 

Measure =
VAR todaysd =
    TODAY ()
VAR d =
    MAX ( 'Table'[date] )
VAR dated =
    DATEDIFF ( d, todaysd, YEAR )
RETURN
    IF (
        ISBLANK ( d ),
        BLANK (),
        IF (
            dated < 1,
            "#FF6B9D",
            IF ( dated >= 1 && dated <= 2, "#F2FF00", "#003AFF" )
        )
    )

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.