Need help in writing logic for color coding either in measure or with additional calculated column

[manojk_pbi]

Hi Friends,

I have one complex requirement to add background color coding in table. I am not sure what is the better option to implement and how this can be achieved. I am seeking for experts advice on this. Your suggestions will be much appreciated. 

Below i have provided the sample table and the logic to be used for color coding.

Logic for color coding : Any milestone date falling between (today-20) days and (today+20) days need be colored
Blue: for upcoming Milestone dates

Green: Completed milestone
Red: Date is missing

Div	Projects	MilestoneStatus	M0-ActualDate	M1-Actual Date	M2-ActualDate	M3-ActualDate	Logic
DIV1	PRJ1	M0	10-Apr-24	10-Jul-24	10-Sep-24	10-Dec-24	Current status of the project is M0, means completed Milestone 0 on 10th Apr 24 and upcoming (M1) is on 10-Jul-24. M1 date is within the range of reporting period so the color is blue.
DIV1	PRJ2	M0	10-Jun-24	10-Jul-24	10-Sep-24	10-Dec-24	Current status of the project is M0, means completed Milestone 0 on 10th Jun 24 and upcoming (M1) is on 10-Jul-24. M0 & M1 date is within the range of reporting period so both will be higlighted. Since M0 is completed marked in Green & M1 is upcoming marked in Blue
DIV1	PRJ3	M1	10-Nov-23	10-Feb-24	10-Sep-24	10-Dec-24	No dates falling in the reporting range, so no color coding
DIV1	PRJ4	M2	10-Nov-23	10-Feb-24	10-Jun-24		Current status of the project is M2, means completed Milestone 2 on 10th Jul 24 and upcoming (M3) has no date defined. So marked in red.

[manojk_pbi]

@lbendlin , thanks for the sample. It helps me to understand and build the similar logic in my application. In case of any queries i will reach out to you.

[lbendlin]

July 10 is more than 20 days out.

To report on things that are not there (your missing date) you need to use disconnected tables and/or crossjoins

[manojk_pbi]

Thanks for your quick response. Pls can you share me the pbix file, will try to understand the concept.

I am still into learning phase of PBI development.