• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidated Column in PBI and Conditional Formatting

Shamash

New Member
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.
 
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.
 
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).
 
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.
 
Back
Top