Hi everyone. I'm building a leave request system in Powerapps for Teams. The employees leave allowance gets reset every year. The year is based on the employees birth month i.e. if an employee's birthday is the 10th of May, on the 1st of May their holiday allowance is reset.
I've created a form to capture the users leave allowance, and how many (if any) days have been taken so far this year, and the users birth month (using a combo box and the items set as Calendar.MonthsLong()).
Within the OnVisible property of the Employee Dashboard screen I've entered the following code to create a variable called varCalcDaysTaken:
Set(varBirthMonth, DateValue("1 " & LookUp('Leave Allowances', 'Employee Name' = User().FullName).MyBirthMonth & " " & Year(Now())));
Set(varBirthMonthDay, Day(varBirthMonth));
// Calculate number of days taken if it's after the 1st day of the user's birth Month, the type of leave is Annual Leave and the status is Accepted
Set(varCalcDaysTaken,
If(
Day(Now()) = varBirthMonthDay,
0,
Sum(
Filter(WaSS_LeaveRequests,
Day('Start Date') > varBirthMonthDay,
Name = User().FullName,
'Type of leave' = (LeaveTypeChoice).'Annual Leave',
RequestStatus = (StatusChoice).Accepted || RequestStatus = (StatusChoice).New
),
'Number of Days')
)
);
Then to output the balance I've entered the following code into the Text property of a label called lbl_Allocation:
If(Text(LeaveType.Selected.Value) = "Annual Leave",
LookUp('Leave Allowances','Employee Name'=User().FullName).Allowance
- LookUp('Leave Allowances','Employee Name'=User().FullName).'Days Taken So Far'
- varCalcDaysTaken
- NumberOfDays.Text,
LookUp('Leave Allowances','Employee Name'=User().FullName).Allowance
- LookUp('Leave Allowances','Employee Name'=User().FullName).'Days Taken So Far'
- varCalcDaysTaken
)
This ALMOST works, I'm struggling with reset on the first day of the users birth month.
I've been using Copilot to help me and this is what it offered:
Set(varBirthDate, DateValue("1 " & LookUp('Leave Allowances', 'Employee Name' = User().FullName).MyBirthMonth & " " & Year(Now())));
Set(varBirthMonth, Month(varBirthDate));
Set(varBirthMonthDay, Day(varBirthDate));
Set(
varCalcDaysTaken,
If(
Now() < DateAdd(varBirthDate, 1),
Sum(
Filter(
WaSS_LeaveRequests,
'Start Date' >= varBirthDate,
'Start Date' < DateAdd(varBirthDate, 1),
Name = User().FullName,
'Type of leave' = (LeaveTypeChoice).'Annual Leave',
RequestStatus = (StatusChoice).Accepted || RequestStatus = (StatusChoice).New
),
'Number of Days'
),
0
)
);
This very close but still doesn't quite do it. After the 'reset' it still takes into account holidays from the previous year.
Can anyone out there help me with this? My brain is melting!
