Conditional color formatting for date range

[bman6074]

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 date

Mark AS Yellow when EOSL Date is not NULL and EOSL Date is between 1 and 2 years away

Mark AS Orange when EOL Date and EOSL Date are NOT NULL, and EOL Date is in the past, and EOSL Date is more than 2 years away

Mark AS Blue when LCS/GA Date is in future or less than a year past

Mark AS Green when the Model in the Technical Services Standard table AS preferred.

[v-frfei-msft]

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.

[bman6074]

Hi, thanks for the help

Unfortunatly I can't provide the PBIX file it has confidential data in it. 

Your script seems close to what I am trying to acheive. Here is a screen shot of what I am trying to achieve but was done via a staing DB and the logic is in a stored procedure on that DB. I would like to do this without a staging DB using DAX. 

Here is the SQL script that may help explain what i am trying to achieve in DAX script.

--Mark AS Retired when EOSL is not NULL and EOSL is within 1 year ahead of the current date
UPDATE snow.RoadMap
SET snow.RoadMap.USAGE_RISK_SCORE = 10
WHERE [EOSL] IS NOT NULL
AND DATEDIFF(MONTH, GETDATE(), CAST([EOSL] AS DATE)) <= 12

--Mark AS Sunset when EOSL is not NULL and EOSL is between 1 and 2 years away
UPDATE snow.RoadMap
SET snow.RoadMap.USAGE_RISK_SCORE = 7
WHERE [EOSL] IS NOT NULL
AND DATEDIFF(MONTH, GETDATE(), CAST([EOSL] AS DATE)) <= 24
AND DATEDIFF(MONTH, GETDATE(), CAST([EOSL] AS DATE)) > 12

--Emerging when before LCS/GA is in future or less than a year past
UPDATE snow.RoadMap
SET snow.RoadMap.USAGE_RISK_SCORE = 0
WHERE [LCS_GA] IS NOT NULL
AND CAST([LCS_GA] AS DATE) > GETDATE()
OR
(DATEDIFF(MONTH, GETDATE(), CAST([LCS_GA] AS DATE)) < 12
AND DATEDIFF(MONTH, GETDATE(), CAST([LCS_GA] AS DATE)) > -12)

--Mark AS Limited Use when EOL and EOSL are NOT NULL, and EOL is in the past, and EOSL is more than 2 years away
UPDATE snow.RoadMap
SET snow.RoadMap.USAGE_RISK_SCORE = 5
WHERE [EOL] IS NOT NULL AND [EOSL] IS NOT NULL
AND CAST([EOL] AS DATE) <= GETDATE()
AND DATEDIFF(MONTH, GETDATE(), CAST([EOSL] AS DATE)) > 24

UPDATE snow.RoadMap
SET snow.RoadMap.USAGE_RISK_SCORE = 5
WHERE [EOSL] IS NOT NULL
AND DATEDIFF(MONTH, GETDATE(), CAST([EOSL] AS DATE)) > 24

--Mark AS Preferred when the OS/Software/Hardware are in the Technical Services Standard doc AS preferred tech
UPDATE snow.RoadMap
SET snow.RoadMap.USAGE_RISK_SCORE = 3
FROM snow.RoadMap a
INNER JOIN
[snow].[InfrastructureStandards] b
ON a.[Manufacturer_Model_OS_Software_name] = b.New_Standard_Version

UPDATE snow.RoadMap
SET snow.RoadMap.[USAGE_RISK] = 'Unsupported/Not Allowed'
WHERE snow.RoadMap.[USAGE_RISK_SCORE] = 10;

UPDATE snow.RoadMap
SET snow.RoadMap.[USAGE_RISK] = 'Sunset'
WHERE snow.RoadMap.[USAGE_RISK_SCORE] = 7;

UPDATE snow.RoadMap
SET snow.RoadMap.[USAGE_RISK] = 'Limited Use'
WHERE snow.RoadMap.[USAGE_RISK_SCORE] = 5;

UPDATE snow.RoadMap
SET snow.RoadMap.[USAGE_RISK] = 'Preferred'
WHERE snow.RoadMap.[USAGE_RISK_SCORE] = 3;

UPDATE snow.RoadMap
SET snow.RoadMap.[USAGE_RISK] = 'Emerging'
WHERE snow.RoadMap.[USAGE_RISK_SCORE] = 0;

This is a screenshot of what I am working with in Power bi desktop. Using the EndofSales, EndOfSoftware, and EndOfSupport for my date range.  If I can have a new Column that is colored and scored like the DB one above that would be the ultimate goal. 

[bman6074 · Accepted solution]

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

)))))