Forum Discussion

Drae's avatar
Drae
New Member
2 years ago

Conditional Formatting, Expiry dates on Matrix Visual

Hi All

 

I have a set of data tables, which I have used to populate a Matrix table to give me a training table. 

 

I added a measure with the following code, to show when things were still in date, within 30 days of expiry, and over 1 year since the date trained:

 

DateStatus =
VAR TodayDate = TODAY()
VAR CourseDate = MAX('EmployeeCourseLog'[CourseDate])
RETURN
SWITCH(
    TRUE(),
    CourseDate < TodayDate - 365, "Pink",
    CourseDate <= TodayDate - 335, "Light Yellow",
    TRUE(), "Light Green")
 
Which worked beautifully and created this:

 

BUT, now Senior management want difference training courses to be refreshed at different times, some every year, some every 2 years.  

I've been trying all sorts to get it to work, but I'm just stuck.  Here is a listof my tables, where you can see the "Expiration Date" Measure nestled in the "EmployeeCourseLog" table. 

 

 

Can anyone help with code that will separate conditional formatting by column. So, for example:

Cyber Security awareness - Green within 1 year, Yellow within 30 days of the year date, and red if past 1 year.

Equality, Diversity - Green within 2 years, Yellow within 30 days of the 2 year date, and red if past 2 years.

 

This is what I started messing with, and I feel like I'm on the right lines, but I'm lacking further knowledge to make it work:

 
Expiration Date =
VAR SelectedColumn = SELECTEDVALUE('CourseTitles'[CourseTitle])
RETURN
    SWITCH(
        SelectedColumn,
        "Cyber Security Awareness", MAX('EmployeeCourseLog'[CourseDate]) + 1 * 365,  // 1 year expiry, "Pink"
        TRUE(), "Light Green")

 

I hope what I'm asking has made sense? Really hope that a) it's possible and b) someone can help.

 

Thanks in advance.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • dporter1111's avatar
      dporter1111
      New Member
      2 years ago

      I am having nearly the same problem and am eager to find a solution.  I need conditional formatting for  date fields in a matrix visual with the value being compared to the compliance date for the specific training category in the Courses table.  If the value date is earlier than the compliance date, the value would be bold red text and if the value date is 1-90 days after the compliance date, the value would be highlighed yellow.  There are some trainings that only have to be completed one time and the compliance date for those is blank in the Courses table.

       

      Due to my organization's IT policies, I'm not able to provide a link to even sample data but have provided screen shots of my report and the relationships established in my data model in hopes that will be enough information.  I can provide more screenshots if it will help to develop a solution.

       

      Thank you,

      D

       

       

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        I cannot assist you if you are unable to provide sample data. I hope someone else can help you further.