ISOWeekNum in Power Apps: Unraveling the Art of Date Week Number Calculation

Calculating week numbers in Power Apps is now seamless with the ISOWeekNum function. Developers and users can effortlessly unravel the art of date-week number calculation using this powerful feature.

Stepping Away from Custom Formulas

Historically, deriving week numbers meant resorting to custom-made formulas. However, Power Apps has streamlined this process by offering the WeekNum and ISOWeekNum functions. These in-built functions effortlessly return week numbers for any provided date. A comprehensive guide on these functions is available on the official Microsoft documentation.

Decoding Week Numbers and ISO Week Numbers

The world doesn’t operate on a single standard for week numbering. Therefore, understanding the different standards is essential. The WeekNum and ISOWeekNum functions help in deciphering week numbers according to the desired system.

Many countries adhere to the “ISO week date” system. Under this system, ISO week 1 is the week of the first Thursday of the year, ensuring that January 4th is always part of this week. A defining feature of this system is the week starting on a Monday. Contrastingly, countries like the US, Canada, and Australia start their week on a Sunday.

Implementing the ISOWeekNum and WeekNum Functions

When you call the ISOWeekNum function, provide a date to get the corresponding ISO week number. In contrast, the WeekNum function requires a date and an optional parameter that denotes the week’s starting day.

An illustrative formula to extract Week Number and ISO Week Numbers for each day of 2021 is:

ForAll(Sequence(365),
With({DateObj:DateAdd(Date(2021,01,01), Value-1)},
     {DateFormatted:Text(DateObj,"ddd dd mmm yy"),
      ISOWeekValue:ISOWeekNum(DateObj),
      StandardWeekNum:WeekNum(DateObj)
     }
   )
)
        

Reference: 

1. ForAll function
2. With function

The result of the above formula can be visualized when mapped to the Items property of a datatable, effectively revealing the week numbers correlation with calendar weeks.

Adjusting the Week Start Day

The WeekNum function offers flexibility by allowing the modification of the week’s starting day via the StartOfWeek enumeration value. For instance, several Middle Eastern countries commence their week on Saturday. The modified formula to cater to this preference is:

ForAll(Sequence(365),
With({DateObj:DateAdd(Date(2021,01,01), Value-1)},
     {DateFormatted:Text(DateObj,"ddd dd mmm yy"),
      ISOWeekValue:ISOWeekNum(DateObj),
      StandardWeekNum:WeekNum(DateObj),
      SatWeekNum:WeekNum(DateObj, Saturday)
     }
   )
)