Power Platform Community Forum Thread Details

DateTime Timezone Issue When Calculating Weekdays-Only or Populating DateTime Value in Canvas App Published Mode

This issue is regarding a DateTime issue where a Date, after it is calculated to only contains weekdays, is mismatched by 1 day when the Date is populated to a created record in Published Mode.

1. Store WeekdayDates and PublicHolidayDates between ToDate_Dp and FromDate_Dp

With(
 {
 // Generate a one-column table of all dates between start date & end date
 varDateRange: ForAll(
 Sequence(ToDate_Dp.Value - FromDate_Dp.Value + 1),
 FromDate_Dp.Value + Value - 1
 )
 },

 //Store Weekday Dates
 ClearCollect(
 WeekdayDates,
 Filter(
 varDateRange,
 Weekday(Value) in [ 2, 3, 4, 5, 6 ]
 )
 );

 //Store Public Holiday Dates
 ClearCollect(
 PublicHolidayDates,
 Filter(
 varDateRange,
 Value in Filter( 'Public Holidays', 'Public Holidays (Views)'.'Active Public Holidays' ).Date
 )
 );
 );

- Taking these dates as example:
- 01 Sep 2024 to 08 Sep 2024 (Sun to Sun)
- 04 Sep 2024 (Wed) as dummy Public Holiday.

*Following incorrect behaviours only occurs in Published Mode*

1. Patch() records where _Date Not In PublicHolidayDates (Correct results)

Patch(
 'Reservation Details',
 ForAll(
 Sequence(
 DateDiff( FromDate_Dp.Value, ToDate_Dp.Value, TimeUnit.Days ) + 1
 ),
 With(
 {
 _Date: DateAdd( FromDate_Dp.Value, Value - 1, TimeUnit.Days )
 },
 
 If(
 Not(_Date in PublicHolidayDates),
 {
 Date: _Date
 }
 );
 )
 )
);

	1-Sep - Sun	2-Sep -Mon	3-Sep - Tue	4-Sep - Wed	5-Sep - Thu	6-Sep - Fri	7-Sep - Sat	8-Sep - Sun
Expected ( Created? )	Yes	Yes	Yes	No	Yes	Yes	Yes	Yes
Results ( Created? )	Yes	Yes	Yes	No	Yes	Yes	Yes	Yes

2. Patch() records where _Date in WeekdaysDates (Incorrect results)

Patch(
 'Reservation Details',
 ForAll(
 Sequence(
 DateDiff( FromDate_Dp.Value, ToDate_Dp.Value, TimeUnit.Days ) + 1
 ),
 With(
 {
 _Date: DateAdd( FromDate_Dp.Value, Value - 1, TimeUnit.Days )
 },
 
 If(
 (_Date in WeekdayDates),
 {
 Date: _Date
 }
 );
 )
 )
);

	1-Sep - Sun	2-Sep -Mon	3-Sep - Tue	4-Sep - Wed	5-Sep - Thu	6-Sep - Fri	7-Sep - Sat	8-Sep - Sun
Expected ( Created? )	No	Yes	Yes	Yes	Yes	Yes	No	No
Results ( Created? )	No	No	Yes	Yes	Yes	Yes	Yes	No

3. Patch() records where _Date Not In PublicHolidayDates and _Date in WeekdaysDates (Incorrect results)

Patch(
 'Reservation Details',
 ForAll(
 Sequence(
 DateDiff( FromDate_Dp.Value, ToDate_Dp.Value, TimeUnit.Days ) + 1
 ),
 With(
 {
 _Date: DateAdd( FromDate_Dp.Value, Value - 1, TimeUnit.Days )
 },
 
 If(
 ((_Date in WeekdayDates) && Not(_Date in PublicHolidayDates)),
 {
 Date: _Date
 }
 );
 )
 )
);

	1-Sep - Sun	2-Sep -Mon	3-Sep - Tue	4-Sep - Wed	5-Sep - Thu	6-Sep - Fri	7-Sep - Sat	8-Sep - Sun
Expected ( Created? )	No	Yes	Yes	No	Yes	Yes	No	No
Results ( Created? )	No	No	Yes	No	Yes	Yes	Yes	No