l Tracked Hours Expression (not including Sunday)

[bjames]

Can any one help with this expression.  My staff use this app to track their hours throughout the week.  I have a virtual column called "CurrentWeekTotalTime" with the following App formula: 

SUM(
SELECT(
Timesheets[Total Time (Hrs)],
AND(
[Staff ID] = [_THISROW].[Staff ID],
YEAR([Date]) = YEAR(TODAY()),
EOWEEK([Date]) = EOWEEK(TODAY()),
[Date] >= (TODAY() - WEEKDAY(TODAY()) + 2)
)
)
)

The problem is, on a Sunday it says 0 hours tracked for this week. 

Our working week is from Monday to Sunday, and so I need the total to include Sunday's hours and then reset on Monday morning.  I'm guessing EOWEEK is the problem, but after 2hours troubleshooting with ChatGPT, I still cant solve it. 

Any help will be greatly appreciated.

Thanks.

[Suvrutt_Gurjar · Accepted solution]

Please try below expression:

SUM(
SELECT(
Timesheets[Total Time (Hrs)],
AND(
[Staff ID] = [_THISROW].[Staff ID],

IF(WEEKDAY(TODAY())=1,
AND([Date]>=TODAY()-6 ,[Date]<=TODAY()),
AND([Date]>=EOWEEK(TODAY())-5 ,[Date]<=EOWEEK(TODAY())+1)

)

)
)
)

[bjames]

Thank you, that worked perfectly.

[Suvrutt_Gurjar]

You are welcome.

If possible, instead of giving heavy calculation to an expression in a VC, you may want to create a " Current_Week" slice with a filter expression 

IF(WEEKDAY(TODAY())=1,
AND([Date]>=TODAY()-6 ,[Date]<=TODAY()),
AND([Date]>=EOWEEK(TODAY())-5 ,[Date]<=EOWEEK(TODAY())+1)

)

Then your VC expression could be simpler such as 

SUM(
SELECT(
Current_Week[Total Time (Hrs)],

[Staff ID] = [_THISROW].[Staff ID]

)

)

The expression will need to evaluate a sub set of records of current week only , instead of evaluating entire table and therby being more sync expensive.