I have a table of data in PowerPivot connected to a sharepoint list. Each row has a date that I need to group by week and count the rows in that week. Is there a DAX formula or other approach to make this happen? I have not found a straight forward solution to this issue. Seems simple enough that there should be a way.
-
With regular PivotTable you can group dates by weeks (or months, years or quarters) by: First - Put the date field in the row labels or column labels. Second - open the context menu on one of the dates in the PivotTable using the right mouse button. Third - select "Group..." from the context menu. Fourth - select only "Days" in the "By" field. Fifth - set the number of days to 7. I guess this will work with PowerPivot but don't have time to check just now and won't be online again any time soon.– LeviApr 7, 2013 at 19:21
Add a comment
|
1 Answer
Can you use the excel WEEKNUM() function in an additional "Weeks" column, then pivot up to the weeks? If you have a date in cell A2, then in B2 you would have WEEKNUM(A2) to get the weeknumber of the week. Then pivot on that.