cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kman42
Helper III
Helper III

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

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!

5 REPLIES 5
rocky09
Impactful Individual
Impactful Individual

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

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!

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
Community Support
Community Support

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
Impactful Individual
Impactful Individual

I am just answering the same.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors