Help with Calculated Column

[rsbin]

Hello,

Could use some guidance on accomplishing the following scenario.  Is using Earlier function the way to go? 

I have a "Payroll" table containing hours worked for each day for each employee.

I need to create a Summary Table such as the following:

Employee_Name	PayWeekNum_2022	Facility	PayWeekStart	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Sunday Prior
John Doe	2	ABC	01/10/2022			11.0	13.0	14.0	15.0	13.0

Need help in creating a Calculated Column for the hours for the Sunday Prior.  (An unusual scenario, but trying to capture payroll errors).  Pay Week starts on Mondays.  So in the following example, I need the hours for John Doe, for Sunday of the previous Pay Week ( i.e. Sunday Jan 9, PayWeekStart of Jan 3, 2022, Day 7).

Appreciate any help and guidance appreciated.

Thanks and Best Regards,

[speedramps · Accepted solution]

Hi rsbin

No need to use EARLIER, this is easier ....

Sunday prior =

-- get this weekdate

VAR myweekdate = SELECTEDVALUE(payroll [weekdate])  

--  gets the previous week
VAR prevweekdate = DATEADD(myweekdate ,-7,day)  

RETURN
-- gets prior sunday by removing the week filter and then getting the previous week

CALCULATE(payroll(Sunday),   
ALL(payroll, payroll[weekdate]),
payroll[weekdate] = prevweekdate)

I am a Power BI volunteer. Please click the thumbs up for me going to the effort of trying to helping you. Then click solved if I fix your problem. One problem per ticket please. If you need to expand or change your problem then please raise a new ticket and click solved on this one so we get kudos. Many thanks. 

[rsbin]

@speedramps ,

Thanks for the response and for the suggestion to use Variables.

Will give this solution a try and will let you know how I make out.

Thanks again and Best Regards,

[speedramps]

I am a Power BI volunteer. Please click the thumbs up now for me going to the effort of trying to helping you. Then click solved soon if I fix your problem so we get kudos. Many thanks.