Hi - i created a timeline matrix in powerbi that shows planned and actual and the overlap (photo below). I used the code below the photo to populate the table. I am wondering what is the best way to create a legend that explains the red is planned, blue is actual and purple is where they overlap. All of the videos use a status to create a dynamic legend, but I don't have a status, thought i could create one based on the dates in my table if necessary. The data is in a stand-alone table with 2 relationships, one to date, and one to project. Any ideas on the most efficient way to create this?
Gantt =
VAR EST_StartDate =
CALCULATE(
Min(PROD_COST_TABLE[Dates]),
REMOVEFILTERS('Date'),
PROD_COST_TABLE[DA_Est_Qty_Complete] >0
)
VAR EST_EndDate =
CALCULATE(
MAX(PROD_COST_TABLE[Dates]),
REMOVEFILTERS('Date'),
PROD_COST_TABLE[DA_Est_Qty_Complete] >0
)
VAR EST_ProjectPeriod =
MIN('Date'[WeekEnding]) >= EST_StartDate
&& Max('Date'[WeekEnding]) <= EST_EndDate
VAR Actual_StartDate =
CALCULATE(
Min(PROD_COST_TABLE[Dates]),
REMOVEFILTERS('Date'),
PROD_COST_TABLE[Qty_Complete] >0
)
VAR Actual_EndDate =
CALCULATE(
MAX(PROD_COST_TABLE[Dates]),
REMOVEFILTERS('Date'),
PROD_COST_TABLE[Qty_Complete] >0
)
VAR Actual_ProjectPeriod =
MIN('Date'[WeekEnding]) >= Actual_StartDate
&& Max('Date'[WeekEnding]) <= Actual_EndDate
VAR EST_Actual_ProjectPeriod =
MIN('Date'[WeekEnding]) >= Actual_StartDate && MIN('Date'[WeekEnding]) >= Est_StartDate
&& Max('Date'[WeekEnding]) <= Actual_EndDate && Max('Date'[WeekEnding]) <= Est_EndDate
VAR RESULT =
SWITCH(
TRUE(),
EST_Actual_ProjectPeriod,3,
Actual_ProjectPeriod,2,
EST_ProjectPeriod,1
)
RETURN
RESULT
