1

I've a table with two columns: 1. Month 2. Week

Data in table is as follows:

Month | Week

1 | 4

2 | 3

3 | 2

4 | 1

5 | 4

6 | 3

7 | 2

8 | 1

9 | 4

10 | 3

11 | 2

12 | 1

Now, based on the date which user provides I need to find the corresponding first day of the week based on the mapping in the table above.

Example-1:

Date from user: 9/29/2019

Now, I will see the month in the date provided by the user and then based on that month; I'll look into the table and see the corresponding week for that month. Here, for month 9(September) the corresponding week will be week 4. Now, since I've the week for that month; I need to find the first date of that week in that month of the year. In this case, it'll be 9/23/2019.

My solution should be 9/23/2019.

Example-2:

Date from user: 10/10/2019

Now, I will see the month in the date provided by the user and then based on that month; I'll look into the table and see the corresponding week for that month. Here, for month 10(October) the corresponding week will be week 3. Now, since I've the week for that month; I need to find the first date of that week in that month of the year. In this case, it'll be 10/21/2019.

My solution should be 10/21/2019.

I'm trying to come up with PowerApps formula for this scenario. Any help will be appreciated.

Fnu Avi
  • 13
  • 1
  • 6
  • How is the week number determined? In your example, you consider the beginning of the week to be 9/23 (Monday). But if that is the case, wouldn't the 4th week be from 9/16 to 9/22? See the image at https://imgur.com/a/Rfxt1YZ for details. – carlosfigueira Sep 30 '19 at 18:18
  • @carlosfigueira In this case, first Monday is considered as the first day of the first week. – Fnu Avi Sep 30 '19 at 19:27
  • If Monday is the first day of the week, then why isn't the 4th week the week from 9/16 to 9/22? If this is the case, then your solution should be 9/16/2019, not 9/23/2019, right? Or in other words, how do you determine that the week from 9/23 to 9/29 is the 4th week of the month? – carlosfigueira Sep 30 '19 at 21:35
  • The first week is from 9/2 - 9/8, second from 9/9 - 9/15, third from 9/16 - 9/22 and fourth from 9/23 - 9/29. – Fnu Avi Sep 30 '19 at 22:23
  • My question is what do you use to determine when a week is the first one? Why isn't the week with 9/1 the first week? Do you only consider a week when all 7 days are in the month? Some other logic? – carlosfigueira Oct 01 '19 at 00:10
  • Yes, the first week starts with the first Monday of the Month. The days before that Monday is not considered in the first week. Like in (10/2019)October, the first week starts with the first Monday of the Month; which is 10/7/2019. Hope, this clarifies. – Fnu Avi Oct 01 '19 at 16:32

1 Answers1

0

With your requirements, you should be able to use this expression (given that the date from the user comes from a date picker; if the date comes from some other source you'll need to adjust the expression):

With(
    { firstDay: Date(Year(DatePicker1.SelectedDate), Month(DatePicker1.SelectedDate), 1) },
    With(
        {
            firstMonday: DateAdd(firstDay, 7 - Weekday(firstDay, StartOfWeek.Tuesday), Days),
            weekNumber: LookUp(MyTable, Month = Month(DatePicker1.SelectedDate), Week)
        },
        DateAdd(firstMonday, 7 * (weekNumber - 1), Days)))

Let's break it down:

  • The first With creates a scope variable called firstDay that holds the first day of the month selected by the user;
  • The second With calculates the first Monday of the month, since this is what determines the week number in your scenario. It does that by adding the number of days until the next Monday to the first day of the month, which was calculated in the above step. It also retrieves the week number from your table (in my example I called it 'MyTable', you would replace it with the appropriate name in your scenario)
  • Finally, given the week number and the first Monday of the month, we can add the appropriate number of days to get the desired result.

Hope this helps!

carlosfigueira
  • 85,035
  • 14
  • 131
  • 171