Forum Discussion

Adham's avatar
Adham
Helper III
6 years ago
Solved

Create bar chart with weeks on axis using end date and calculated start date

Hello,   I am trying to create a bar chart (for project management) with weeks on my axis and number of hours spent working each week as my values. The aim of this is to see if a certain person is ...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    OK Adham , I want to make the comment that this took quite a bit of thought and work. But, I believe I have what you are looking for finally. See attached PBIX file. Had to create the estimated Start Date and then create a table like this:

     

    Employee Weeks = 
        SELECTCOLUMNS(
            ADDCOLUMNS(
                FILTER(
                    ADDCOLUMNS(
                        GENERATE(
                            DISTINCT('Table'[Employee Id]),
                            CALENDAR(MIN('Table'[Task Start Date]),MAX('Table'[Task Due Date]))
                        ),
                        "__IsWeekDay",IF(WEEKDAY([Date],3) < 5,TRUE(),FALSE())
                    ),
                    [__IsWeekDay] = TRUE()
                ),
                "__Work Hours",8
            ),
            "Employee Id",[Employee Id],
            "Date",[Date],
            "Work Hours",[__Work Hours]
        )

     

    In this new table, I created these columns:

     

    Week = WEEKNUM([Date])
    
    Total Work Hours = 
        VAR __Table = 
            ADDCOLUMNS(
                FILTER(
                    'Table',
                    'Table'[Employee Id] = 'Employee Weeks'[Employee Id]
                ),
                "Must Work",IF([Date] >= [Task Start Date] && [Date] <= [Task Due Date],1,BLANK())
            )
        VAR __TotalHours = SUMX(__Table,[Must Work]) * [Work Hours]
    RETURN
        IF(ISBLANK(__TotalHours),BLANK(),__TotalHours)

     

    I created the bar chart using a categorical Y Axis to remove blank values. Two different bar visuals, one tied to a slicer and one not tied to a slicer.