Calculated Column for Colour Rule

[Anonymous]

Hi Experts

Need a DAX measure as a calculated column to work out the following rule inorder to colour a bar chart visual, here is the rule

1. Todays Date is 10.11.21 < then todays date "red",
2. Next month ie Dec 21 "Amber", When we are in Dec 21 Then Jan 22 is Amber only and so on

3. Any date in the future excluding next month ie Dec 21 (which would be Amber as per point 2) "Green"

https://www.dropbox.com/s/bwxvb5wilqrqlge/Sample_File.pbix?dl=0 

the measure i have and is not working is

a measure =

Switch(true() ,

max('Date'[Date]) < Today() , "Red",

max('Date'[Date]) < eomonth(Today(),1) , "Amber",,

"Green"

)

[v-xiaoyan-msft]

Hi @dynamicssquare ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , what happen remaining nov ?

a measure like

a measure =

Switch(true() ,

max('Date'[Date]) < Today() , "Red",

max('Date'[Date]) >= eomonth(Today(),0) +1 && max('Date'[Date]) <= eomonth(Today(),1)  , "Amber",

"Green"

)

a measure =

Switch(true() ,

max('Date'[Date]) < Today() , "Red",

max('Date'[Date]) < eomonth(Today(),0)  , "Green" // remaining nov color , change that

max('Date'[Date]) >= eomonth(Today(),0) +1 && max('Date'[Date]) <= eomonth(Today(),1)  , "Amber", //dec , next month

"Green"

)

[dynamicssquare · Accepted solution]

With conditional formatting for tables in Power BI Desktop, you can specify customized cell colors, including color gradients, based on field values. You can also represent cell values with data bars or KPI icons, or as active web links. You can apply conditional formatting to any text or data field, if you base the formatting on a field that has numeric, color name or hex code, or web URL values.

Suggested Article: Easy Steps To Generate Interactive Power BI Reports

[Anonymous]

If its nov then assume in the past.....even thow we are still in Nov and the month has not completed...

[amitchandak]

@Anonymous , try like

a measure =

Switch(true() ,

max('Date'[Date]) <= eomonth(Today(),0) , "Red" // less than nov

max('Date'[Date]) >= eomonth(Today(),0) +1 && max('Date'[Date]) <= eomonth(Today(),1) , "Amber", //dec , next month

"Green"

)

[Anonymous]

i Think the error is that the above is a measure and i need a calculted column

[Anonymous]

Hi Amit - Sorry to be a pain but can you implement it to the PBIX file i attached cannot get it to work...