Forum Discussion
Create bar chart with weeks on axis using end date and calculated start date
- 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.
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.
Hello Greg,
This is exactly what i was looking for! Thank you very much for this! I really do appreciate your help and effort to assist me!