For a Power App that I’m building to be able to book hours per month, I wanted to divide the month into ISO week numbers, and for each week number, a user can enter their hours.
To achieve this, I needed to determine which ISO week numbers fall within a month and what the start date is for a selected week number.
Unfortunately, there is no standard function available within Power Apps for this, so I have built it myself.
My built Power App
The solution
To retrieve the start and end dates of a month, I have used the following code:
Set(
TimesheetStartDate,
Date(
Year(dte_TimesheetMonth.SelectedDate),
Month(dte_TimesheetMonth.SelectedDate),
1
)
);
Set(
TimesheetEndDate,
Date(
Year(dte_TimesheetMonth.SelectedDate),
Month(dte_TimesheetMonth.SelectedDate) + 1,
1
) - 1
);
Now, I am retrieving the start and end dates of the selected month in TimesheetStartDate
and TimesheetEndDate
.
Next, I will fetch all the ISO week numbers using the following code:
ClearCollect(
WeeknumbersDate,
AddColumns(
ForAll(
Sequence(47),
Date(
Year(dte_TimesheetMonth.SelectedDate),
Month(dte_TimesheetMonth.SelectedDate) +1,
10
) - Value
)
,
"Weeknumber",
ISOWeekNum(Value)
)
);
This code retrieves all the dates and ISO week numbers of the current selected month, as well as a few dates from the following month and the month before the current month. It stores them in the collection WeeknumbersDate
. This is necessary because a week number may partially fall in the previous or next month.
Then, I have a gallery where I populate the Items
with the following code to fill in all the ISO week numbers that fall within a month:
Distinct(ForAll(
Sequence(
Day(TimesheetEndDate)
),
ISOWeekNum( Date(
Year(TimesheetEndDate),
Month(TimesheetEndDate),
Value
))
),Value)
In the OnSelect
property, I have provided the following code to store the selected week number:
Set(SelectedWeekNumber, ThisItem.Value);
To determine the first and last day within which a week number falls, I use the following code:
Set(
StartDateWeek,
Last(Filter(WeeknumbersDate,Weeknumber = SelectedWeekNumber)).Value
);
Set(
EndDateWeek,
DateAdd(
StartDateWeek,
6,
TimeUnit.Days
)
);
This code looks in the WeeknumbersDate
collection for all dates where SelectedWeekNumber
occurs and then retrieves the last one, which represents the start date of the week 😀.
This way, you can retrieve all week numbers and their corresponding dates.
Closing word
I want to thank Matthew Devaney for his inspiration in building this Power App.
Leave a Reply