Help using DAX to determine IF any one of project end dates is within a year

[kman42]

I am trying to create a KPI that will fill in the background of a cell with one of three colors depending on the earliest end date of any project assigned to a person. I am displaying a table like the following:

Person              # Projects
A                       3
B                       1
C                       2

The number column is just a DAX formula counting the number of distinct projects in the Project Table assigned to each Person. The Project Table also includes a field called Project_End_Date. If any of A's projects end within 18 months, I would like the background of the cell to yellow; if any of them end within 9 months, I would like the background of the cell to be red (taking precedence over the yellow).

I know how to use SWITCH to set the colors and assign conditional formatting, but I don't know how to get all of the Project_End_Dates and make the determination of whether any of them is less than 18 months and then 9 months. Could someone help me with the logic and DAX functions?

Thanks!

[rocky09]

can you please post some sample data and an example of out you are looking for.

[kman42]

Project Table

Project_Number               Person                  Project_End_Date
1                                       A                           10/1/2022
2                                       A                           12/1/2019
3                                       A                           5/1/2020
4                                       B                           1/31/2021
5                                       B                           12/1/2021
6                                       B                           5/1/2020
7                                       C                           6/1/2022
8                                       C                           7/1/2022

Output visual

Person                    # Projects
A                             3 (red background)
B                             3 (yellow background)
C                             2 (no background)

The '3' for A should have a red background since the earliest project end date is less than 9 months away (Project_Number 2). The '3' for B should have a yellow background since the earliest project end date is less than 18 months (Project_Number 6). The '2' for C should have no background since none of the projects ends in less than 18 months.

I hope that is clear. Thanks!

[kman42]

I figured it out. I created a variable to calculate the MIN of the end dates for the filtered table and then used that in the SWITCH function to set the color. Amazing how asking the question can help you figure out the solution!

[v-xicai]

Hi @kman42 ,

Glad to hear that you have resolved the problem by yourself. Would you please mark your sharing resolution as an answer so that it can help others having similar issue?

Best Regards,

Amy

[rocky09]

I am just answering the same.