Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a table which looks similar to the below.
PROJECT | PHASE | FORECAST PHASE 1 | FORECAST PHASE 2 | FORECAST PHASE 3 | FORECAST PHASE 4 |
A | PHASE 1 | {DATE} | {DATE} | {DATE} | {DATE} |
B | PHASE 2 | {DATE} | {DATE} | {DATE} | {DATE} |
C | PHASE 3 | {DATE} | {DATE} | {DATE} | {DATE} |
D | PHASE 2 | {DATE} | {DATE} | {DATE} | {DATE} |
I am searching for a way to apply conditional formatting to highlight any dates in the past, but only for the next phase.
For example, if Project A is in Phase 1, I want the formatting to only apply to the column relevant to Forecast Phase 2, and I need to highlight if the date is in the past.
Any assistance is appreciated.
Thank you.
Solved! Go to Solution.
Hi,@kristi_in_heels I am glad to help you.
Here are my suggestions.
suggestion1:
Create a corresponding measure for the corresponding columns
M_PHASE2 =
SWITCH(TRUE(),
MAX('ProjectTable'[PHASE])="PHASE 1",IF(SELECTEDVALUE(ProjectTable[FORECAST PHASE 2])<TODAY(),1,2),
0
)
M_PHASE3 =
SWITCH(TRUE(),
MAX('ProjectTable'[PHASE])="PHASE 2",IF(SELECTEDVALUE(ProjectTable[FORECAST PHASE 3])<TODAY(),1,2),
0
)
M_PHASE4 =
SWITCH(TRUE(),
MAX('ProjectTable'[PHASE])="PHASE 3",IF(SELECTEDVALUE(ProjectTable[FORECAST PHASE 4])<TODAY(),1,2),
0
)
Use the corresponding Measure value for each column to set the background color of that column.
suggestion2
You can also apply a background color to newly created calculated columns by uniformly extracting the values of the columns that satisfy the conditions
create calculate column
Next Phase Date =
SWITCH(
TRUE(),
ProjectTable[PHASE] = "PHASE 1", ProjectTable[FORECAST PHASE 2],
ProjectTable[PHASE] = "PHASE 2", ProjectTable[FORECAST PHASE 3],
ProjectTable[PHASE] = "PHASE 3", ProjectTable[FORECAST PHASE 4],
BLANK()
)
create measure:
IF_expiration_date =
IF(MAX('ProjectTable'[Next Phase Date])<TODAY(),
1,0)
I hope my test results can bring you good ideas and you can choose the right way according to your needs
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much.
I ended up going with a different solution I discovered, but I appreciate your input and suggestions and will keep these on hand if I need to achieve this result again.
For reference, I created an individual measure per Phase:
Hoping someone can assist me further wiuth this measure.
The formatting is working as intended, but there is 1 column I need to only apply the formatting to based on the text within a separate column.
Essentially, I want only projects marked "Type A2" to be coloured by the conditional formatting in only one of the columns.
Eg
Column 1 - conditional formatting applied to all projects
Column 2 - Conditional formatting filtered to only be applied to only A2 projects
I can't filter the entire dataset as this will then lose the data from some of the other columns we want to see.
Thank you
Hi,@kristi_in_heels I am glad to help you.
Here are my suggestions.
suggestion1:
Create a corresponding measure for the corresponding columns
M_PHASE2 =
SWITCH(TRUE(),
MAX('ProjectTable'[PHASE])="PHASE 1",IF(SELECTEDVALUE(ProjectTable[FORECAST PHASE 2])<TODAY(),1,2),
0
)
M_PHASE3 =
SWITCH(TRUE(),
MAX('ProjectTable'[PHASE])="PHASE 2",IF(SELECTEDVALUE(ProjectTable[FORECAST PHASE 3])<TODAY(),1,2),
0
)
M_PHASE4 =
SWITCH(TRUE(),
MAX('ProjectTable'[PHASE])="PHASE 3",IF(SELECTEDVALUE(ProjectTable[FORECAST PHASE 4])<TODAY(),1,2),
0
)
Use the corresponding Measure value for each column to set the background color of that column.
suggestion2
You can also apply a background color to newly created calculated columns by uniformly extracting the values of the columns that satisfy the conditions
create calculate column
Next Phase Date =
SWITCH(
TRUE(),
ProjectTable[PHASE] = "PHASE 1", ProjectTable[FORECAST PHASE 2],
ProjectTable[PHASE] = "PHASE 2", ProjectTable[FORECAST PHASE 3],
ProjectTable[PHASE] = "PHASE 3", ProjectTable[FORECAST PHASE 4],
BLANK()
)
create measure:
IF_expiration_date =
IF(MAX('ProjectTable'[Next Phase Date])<TODAY(),
1,0)
I hope my test results can bring you good ideas and you can choose the right way according to your needs
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |