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?
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
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!
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!
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?
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates