Forum Discussion

SCNCKS1's avatar
SCNCKS1
Icon for Helper I rankHelper I
3 years ago
Solved

Formatting to create a Gantt Chart

Hi, I am attempting to create a Gantt chart in powerbi. I have a production table that is filtered by a date table and a Project table (needed to have the project expenses filtered correctly for a different visual). The dax below works if I remove the selected value part of the measure, with the exception that it includes all production units even if there is no data in them. The issue appears to be the column that doesn't have a date, Any ideas on how i would filter those out?

 

my dax is:

 

CF Gantt =
VAR StartDate =
CALCULATE(
    Min(PROD_COST_TABLE[Dates]),
    REMOVEFILTERS('Date'),
    SELECTEDVALUE(PROD_COST_TABLE[ProductionUnit]),
    PROD_COST_TABLE[DA_Est_Qty_Complete] >0    )

VAR EndDate =
CALCULATE(
    MAX(PROD_COST_TABLE[Dates]),
    REMOVEFILTERS('Date'),
    SELECTEDVALUE(PROD_COST_TABLE[ProductionUnit]),
    PROD_COST_TABLE[DA_Est_Qty_Complete] >0    )
VAR ProjectPeriod =
MIN('Date'[WeekEnding]) >= StartDate
&& Max('Date'[WeekEnding]) <= EndDate

VAR RESULT =
If(
    ProjectPeriod,
    1)
RETURN
RESULT
 

 

 


 

Need Help
Tutorial Requests
  • SCNCKS1's avatar
    SCNCKS1
    2 years ago

    I decided to add estimate and actual timelines. the code i used was:

     

    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

  • SCNCKS1's avatar
    SCNCKS1
    Icon for Helper I rankHelper I
    2 years ago

    I decided to add estimate and actual timelines. the code i used was:

     

    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