Power Fx: Int, Trunc, WeekNum, and ISOWeekNum functions
We have a bit of a tradition on the Power Fx team. As part of onboarding, new team members add a few functions to get up to speed on how the system works internally. Their learning is your gain in more ways than one!
In this installment, we have added four new functions to Power Fx formulas, straight from their definitions in Excel:
- Int and Trunc fill out our rounding functions. These are common functions in Excel that until now one would need to translate to one of our Round functions. They are the same for positive numbers, but for negative numbers Int rounds away from zero and Trunc rounds toward zero. For example, Int( -2.4 ) returns -3 while Trunc( -2.4 ) returns -2.
- WeekNum and ISOWeekNum have been common customer requests. These functions return the week number in the current year which is helpful for many business date calculations. WeekNum starts with the week containing January 1, while ISOWeekNum starts with the first Thursday of the year. For example using August 11, 2021, WeekNum( Today() ) returns 33 while ISOWeekNum( Today() ) returns 32 as we haven’t made it to Thursday of this week yet.
These functions are rolling out now and are available in version 3.21074 or later.
One thing we did not copy from Excel is the treatment of 1900 as a leap year. Power Fx needs to work with dates in Dataverse, SQL Server, and other sources that reach further back into history than 1900 and we didn’t want a discontinuity at 1900.
As always, your feedback on the Power Apps community forum is very helpful to us. The requests and discussion on WeekNum and ISOWeekNum that appeared there directly influenced our decision to implement these functions natively.
