Forum Discussion
Solved
DAX Table Summary By Week
Hi all,
How can I create a DAX table which summarises the below data by week?
The DAX table would have a sum of each candidates rest time by week.
Week, Candidate, Sum of Rest Time
Hi HenryJS ,
You can create a weeknum column in your table:
weeknum = "WEEK"&WEEKNUM('Table'[Date],2)Then create summarize table:
Table2 = SUMMARIZE ( 'Table', 'Table'[Worker Name], 'Table'[weeknum], " Sum of Rest Time", SUM ( 'Table'[Rest Time] ) )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
4 Replies
HenryJS , You can create Date table week data and group data.
Columns in date table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2) Week Number = WEEKNUM([Date],2) Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number]) Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense) Week name = [Week Start date] & " to "& [Week End date] Weekday = WEEKDAY([Date],2) WeekDay Name = FORMAT([Date],"ddd")To sum up duration you can follow
amitchandak I've added the below week ending column
Week Ending = 'Table'[Date]+ 7-1*WEEKDAY('Table'[Date],2)But need a measure which sums the 'Rest Time' column. It's a time data type columnHenryJS , you might not be able to sum time. That is why I shared two links. So basically add as number and display as time
