Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
SCNCKS1
Helper I
Helper I

Legend for a matrix timeline in powerbi

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?

 

SCNCKS1_0-1695303297530.png

SCNCKS1_1-1695303574465.png

 

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
2 REPLIES 2
christinepayton
Super User
Super User

You could create a dimension table with "enter data" to create the legend values you are using as rules, then relate it to the field you are using in your rules (or add a conditional column on the values in PQ) and drop it in the legend well. 

I don't see the legend well in the matrix visual or did i misunderstand your suggestion?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.