Forum Discussion
Help with Calculated Column
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,
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.
3 Replies
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.
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,
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.
