Forum Discussion
Help with summing values by week
I am working with an SQL database provided by a vendor to build some power BI reports. So far it has been all good but now I need some help.
I want to calculate the billable time that people have worked over a given week from their timesheet.
The catch is that the User Timesheet Table does not have a date field, it only has a week start and week end.
Fields are:
FACTUserTimesheet [WeekStart]
FACTUserTimesheet [Week End] and
FACTUserTimesheet [Billableminutes]
We have a Date table that has date, the week number already added and FirstofWeek and LastofWeek values
Fields are
DIMDateExtended [Date]
DIMDateExtended [FirstofWeek]
DIMDateExtended [LastofWeek]
DIMDateExtended [Week]
I want to create a sum of the billable minutes for every week and the cumulative values in a table like below.
| Week | Start of Week | Billable Minutes | Billable Hours | Cumulative Values |
| 1 | 20/12/2024 | 450 | 7.5 | 7.5 |
| 2 | 6/01/2025 | 700 | 11.67 | 19.17 |
| 3 | 13/01/2025 | 500 | 8.33 | 27.50 |
| 4 | 20/01/2025 | 623 | 10.38 | 37.88 |
I need help with the mesures for Billable Minutes, Billable Hours and Cumulative values based on the week.
Any guidance is really appreciated as I am not that experienced with Dax and this is getting a bit beyond my skills.
Create a relationship between FACTUserTimesheet [WeekStart] and DIMDateExtended [FirstofWeek].
This allows for the filter context to come into use - see https://www.sqlbi.com/articles/filter-context-in-dax/
You can either use an Implicit Measures (drag FACTUserTimesheet [Billableminutes] to the data section and summarise by SUM), or a measure:
Billable Minutes = SUM(FACTUserTimesheet [Billableminutes])see https://radacad.com/explicit-vs-implicit-dax-measures-in-power-bi if you're unsure on implicit vs explicit measures.
For Billable Hours, it would be best to use a measure:
Billable Minutes = DIVIDE(SUM(FACTUserTimesheet [Billableminutes]), 60)And the easiest way to get the cumulative billable hours would be to use a visual calculation with the RUNNINGSUM() function- https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview
1 Reply
Create a relationship between FACTUserTimesheet [WeekStart] and DIMDateExtended [FirstofWeek].
This allows for the filter context to come into use - see https://www.sqlbi.com/articles/filter-context-in-dax/
You can either use an Implicit Measures (drag FACTUserTimesheet [Billableminutes] to the data section and summarise by SUM), or a measure:
Billable Minutes = SUM(FACTUserTimesheet [Billableminutes])see https://radacad.com/explicit-vs-implicit-dax-measures-in-power-bi if you're unsure on implicit vs explicit measures.
For Billable Hours, it would be best to use a measure:
Billable Minutes = DIVIDE(SUM(FACTUserTimesheet [Billableminutes]), 60)And the easiest way to get the cumulative billable hours would be to use a visual calculation with the RUNNINGSUM() function- https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview