PIVOTBY a Date Range with Empty Data
Not sure if this is possible, but I have weekly timecard data that I am dumping into a table and I want to PIVOTBY Employee and Date. This is easy enough. However, most of the hours are on Mon-Fri, but occasionally hours are submitted on the weekend. For consistency sake, I would like to report the entire week, even if there is no data in the table for those weekend dates. Something like the report below...
Right now I am accomplishing this by using SEQUENCE(1,7,MIN(TableHours[Date])-MOD(MIN(TableHours[Date])-1,7)) to generate the column headers for the dates, then a UNIQUE() for the employees and SUMIFS to populate the hours, and then another line to total each day. I am trying to figure out if I can replace all this by using a single function.
[link] [comments]
Want to read more?
Check out the full article on the original site