Help with summing values by week

[AmandaHore]

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. 

[vicky_ · Accepted solution]

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