Something like this, you mean?
![]()
It requires a bit of tweaking and don't know if its stable/always shows the correct result.
Do keep in mind that your formula will not work that way. This is due to the fact that you have added a date field in the columns. You should use PREV() in that case. You could try the formula below and do a grand total on column level and change the total-type to SUM for that formula
![]()
This will only work if the combination of all rows covers all days. If no one works on sunday, it will never be shown. This would require an alteration in the cube. This is the formula I used to achieve the above example:
(([# of unique EmployeeID],all([Days in Date])) +
case
when (([# of unique EmployeeID],all([Days in Date]))+SUM([Minutes])) = 1 then 480
else SUM([Minutes])
end - ([# of unique EmployeeID],all([Days in Date])))/60
This is what I did:
1. I added ([# of unique EmployeeID],all([Days in Date])) the create a baseline. This counts the unique ID of the employee regardless of the fact if the employee wrote hours for that day. This will create a 1 value for the days without any hours
2. With the CASE-statement we want to filter out days that have hours. when (([# of unique EmployeeID],all([Days in Date]))+SUM([Minutes])) = 1 then 480 by adding essentially 1 + minutes we can divide between worked and not worked days. Worked days will have 1 + X minutes while non worked days will have 1+0=1 minute
3. We subtract ([# of unique EmployeeID],all([Days in Date])) in order to get back to the accurate number of minutes (or 480 if empty)
4. We divide by 60 to get the hours
5. Enable Grandtotal on the column (e.g. date) field
6. Enable Total on the formula and select SUM instead of Auto.
I have not worked out your second requirement of your equation. However perhaps this could help you in the right direction 🙂