Conditional Formatting Dates based on Today and End Fiscal Year

[FsmA]

I have a table of mostly dates. The first couple columns are employee identifiers (name, employee ID, Job, Department). The rest of the columns are renewable qualifications and when each one is expiring. Some quals are to be renewed annually, some on a two or three year cycle. The table shows the date when each qual for each employee is expiring.

I want to conditionally format the dates based on whether they are already expired (RED), or if they are current but will expire before end fiscal year - 31 Mar - (ORANGE), or if they will remain current past end current FY (GREEN). In excel, conditional formatting accepts formulas so the logic I used was as follows:

If DATE <= Today() | Color RED
Else if DATE > Today()
    If current month is greater than 3
         if DATE > 31, Mar, CurrentYear+1 | Color GREEN
        Else if DATE <= 31, Mar, CurrentYear+1 | Color ORANGE
    Else If current month is less than or equal to 3
        if DATE > 31, Mar, CurrentYear | Color GREEN
        Else if DATE <= 31, Mar, CurrentYear | Color ORANGE

This doesn't work in PowerBI since conditional formatting wants specific values. Any assistance on how this color-coding may be achieved in PBI?

Sample Data:

[_AAndrade]

Hi,

Can you share some data or show how the data is organized? I can try to help you

[FsmA]

I have updated the original post with a screenshot of sample data. Had to remove sensitive info, but the jist of it is there.

[_AAndrade]

Hi,

Please see if this is what you are looking for: