How to do conditional formatting to card visuals based on the Date condition and Other measure value

[VenkateshV]

Attached picture has the details of the report layout based on the sample data.

I have to apply the conditional formating based on the EmployeeDOJ and GoalMetrics columns.

EmpDOJ is equql to current month.So it should be YELLOW in color.No 'Goal Metrics' applied
EmpDOJ is less than current month i.e joined before current month So 'GoalMetrics' to be applied and it should be GREEN if the 'GoalMetrics' is >=3
EmpDOJ is less than current month i.e joined before current month So 'GoalMetrics' to be applied and it should be RED if the 'GoalMetrics' is =<3
All the metrics for the future months in that Fiscal year has to be 'GREY' in color with no values inside
Anyone can explain how this can be achieved in Power BI report. Thanks.

[v-yetao1-msft · Accepted solution]

Hi @VenkateshV 

Create a measure like this :

Measure=SWITCH(TRUE(),MONTH(SELECTEDVALUE('Table'[EmpDOJ]))=MONTH(TODAY()),"Yellow",
MONTH(SELECTEDVALUE('Table'[EmpDOJ]))<MONTH(TODAY()) && SELECTEDVALUE('Table'[GoalMetrics])>=3,
"Green",MONTH(SELECTEDVALUE('Table'[EmpDOJ]))<MONTH(TODAY()) && SELECTEDVALUE('Table'[GoalMetrics])<3,"Red","Grey")

Then in Matrix visual Format ,choose the field that need condition formatting and turn on the Background color . Select Field value in Format by ,Measure in Based on field in the pop-up setting box .

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[amitchandak]

@VenkateshV , You can create a color measure like this and use that in conditional formatting using field value option

Switch(True() ,

eomonth(Max(Table[EmpDOJ ]),0) < eomonth(today(),-3)+1 , "Red"

eomonth(Max(Table[EmpDOJ ]),0) < eomonth(today(),-1) +1 , "Green"

eomonth(Max(Table[EmpDOJ ]),0) = eomonth(today(),0) , "Yellow"

)

refer

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4