Group by Week for multiple tables

[RachelN]

Hi guys,

I hope you are all safe during this time.

I'm a new Power BI user and hope that you can help me with the best approach to solve this.

I have three table name Timesheet (include Staff Name, working date, working hours per date); Trello Work (includes Staff Name, working date, completed task items, hours per task item); Non-Trello work (includes Staff Name, working date, Additional ask types, hours per tasks).

I want to create a visualization to show Timesheet hours, Trello Work hours, Non-Trello work hours every week.

My relationship setting is Timesheet table is joined with Trello Work and Non-Trello work table by Staff name.

I created a new column "Week of Year" in Timesheet table; then use matrix table in visualization to show Timesheet hours, Trello Work hours, Non-Trello work hours every week. The issue is the Week of year of works for Timesheet hours, and not the other tables (Week 6 & 9 are the same for Trello hours, Week 6 & 7 are the same for Non-trello hours)

Please let me know if you need additional information. Thank you in advance!

Best regards,

[v-lid-msft · Accepted solution]

Hi @RachelN ,

We can try to create a calendar dim-table to connect other tables and use WeekNum column of it as the row of visual:

CalendarTable =
ADDCOLUMNS ( CALENDARAUTO (), "WeekNum", WEEKNUM ( [Date] ) )

We can also Create Staff dim-table by using following dax:

Staff =
DISTINCT (
    UNION (
        DISTINCT ( 'Timesheet'[Staff Name] ),
        DISTINCT ( 'Trello Work'[Staff Name] ),
        DISTINCT ( 'Non-Trello work'[Staff Name] )
    )
)

Best regards,

[v-lid-msft · Accepted solution]

Hi @RachelN ,

We can try to create a calendar dim-table to connect other tables and use WeekNum column of it as the row of visual:

CalendarTable =
ADDCOLUMNS ( CALENDARAUTO (), "WeekNum", WEEKNUM ( [Date] ) )

We can also Create Staff dim-table by using following dax:

Staff =
DISTINCT (
    UNION (
        DISTINCT ( 'Timesheet'[Staff Name] ),
        DISTINCT ( 'Trello Work'[Staff Name] ),
        DISTINCT ( 'Non-Trello work'[Staff Name] )
    )
)

Best regards,

[RachelN · Accepted solution]

Hi @v-lid-msft @amitchandak, thank you so much for your kind support! I've achieved the outcome that I want for my data. I really appreciate your help!

[amitchandak]

Create a common date dimension. Join with other tables have you week calculation there and then use that for visual.

Also refer: https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0