Grouping hours by time period and memberID

[slounsbury]

I'm trying to create a report for our HR team to automate our payroll system but am having trouble with a column grouping pay periods.

Here is what the table currently looks like:

MemberID	Hours_Logged	Timesheet_Period
781	30	2020-03-01 to 2020-03-07
781	10	2020-03-08 to 2020-03-15
781	26	2020-03-01 to 2020-03-07
781	14	2020-03-08 to 2020-03-15

Here is the outcome I am looking for:

MemberID	Hours_Logged	Timesheet_Period	WeekTotal
781	30	2020-03-01 to 2020-03-07	40
781	26	2020-03-08 to 2020-03-15	36
781	10	2020-03-01 to 2020-03-07	40
781	10	2020-03-08 to 2020-03-15	36

I've been working on this report for a while and have hit a road block with this part I also need this column to be included in some of my other calculations for Overtime, PTO, ect. Any help is appreicated. Thanks! 

[cmilligan262 · Accepted solution]

@slounsbury 

Try:

Week Total = Calculate(Sum('Table'[Hours_Logged]),Filter('Table','Table'[Timesheet_Period]=EARLIER('Table'[Timesheet_Period])&&'Table'[Member ID]=EARLIER('Table'[Member ID])))

[slounsbury]

@cmilligan262 Thank you! This worked exactly as expected. After adding this I realized I need to have only one value per timesheet period. Would it be possible to have it appear like the tablet below? There is also a date column in this table (Last_Updated) that is available if it can be done based on which entry is first, if that makes sense?

MemberID	Hours_Logged	Timesheet_Period	WeekTotal
781	30	2020-03-01 to 2020-03-07	40
781	26	2020-03-08 to 2020-03-15	36
781	10	2020-03-01 to 2020-03-07	0
781	10	2020-03-08 to 2020-03-15	0

[cmilligan262]

@slounsbury 

Not sure if I can get it to show 0's like you want but try creating a summarized table so it just shows the week total.

Create a new table with the formula

Summarized Table = SUMMARIZE('Table','Table'[MemberID],'Table'[Timesheet_Period],"Week Total",Sum('Table'[Hours_Logged]))

Depending on the output you want and what you're trying to create this may be better done as a measure.