Consolidated Column in PBI and Conditional Formatting

[Shamash]

Hello folks,

I want to create a consolidated column in PBI by utilizing a forecast and actual dates column for a specific task , if a task is actualized the consolidated column show the date from actualized column otherwise it should keep showing the dates from forecast column so far it sounds simple and can be achievable using if else statements though the complexity is added when I want to conditionally format the cell in the consolidated table as green if it is actualized , use color red when the forecast date is overdue and use color orange when the forecast date is upcoming in the next 7 days. Any guidance in how to achieve it in one consolidated column in PBI is much appreciated.

 

[Chihiro]

Without sample dataset and mockup of your desired result (created manually). It's hard to help you. I'd recommend uploading a sample workbook here.

 

[Shamash]

Without sample dataset and mockup of your desired result (created manually). It's hard to help you. I'd recommend uploading a sample workbook here.

Hi Chihiro ,

Below is the expected table in the PBI :

​

​

 

[Chihiro]

So it's simply matter of creating column.
Something like...
= if [Milestone 1 Actual Date] = "" then [Milestone 1 Forecast Date] else [Milestone 1 Actual Date]

Then for CF, I'd recommend that you add another column to use as condition column.
= if [Milestone 1 Actual Date] <> "" then 0 else if Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - [Milestone 1 Actual Date]) < 0 then 1 else if Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - [Milestone 1 Actual Date]) < 8 then 2 else null

Then just use this column as condition in your visual (i.e. if 0 then green, if 2 orange, if 1 then red).

 

[Shamash]

So it's simply matter of creating column.
Something like...
= if [Milestone 1 Actual Date] = "" then [Milestone 1 Forecast Date] else [Milestone 1 Actual Date]

Then for CF, I'd recommend that you add another column to use as condition column.
= if [Milestone 1 Actual Date] <> "" then 0 else if Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - [Milestone 1 Actual Date]) < 0 then 1 else if Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - [Milestone 1 Actual Date]) < 8 then 2 else null

Then just use this column as condition in your visual (i.e. if 0 then green, if 2 orange, if 1 then red).

Thanks Chihiro.