Power Platform Community Forum Thread Details

Hello,

I'm needing some help on this calculation in power apps and cannot find any solutions in previous threads!

I have a list of dates to input working shifts and need to display the number of consecutive days worked between rest days. In the table it would look something like this. The inputs are entered by the user into a collection.

Date (input)	Work type (input)	Consecutive Days (formula)
01/08/2022	Dayshift	1
02/08/2022	Dayshift	2
03/08/2022	Dayshift	3
04/08/2022	Rest	0
05/08/2022	Dayshift	1
06/08/2022	Dayshift	2
07/08/2022	Rest	0

Thanks in advance

Categories:

Building Power Apps

Disclaimer, I've not tried this before in PowerApps, so I hope others will jump in if it's not optimal, or the most "PowerApps" way of calculating.

I figure once you know the most recent day of a different Type, you just need to get the DateDiff:

Test Data:

ClearCollect(
 colWorkDays,
 {Date: DateAdd(Now(), 0, Days), Type: "Dayshift"},
 {Date: DateAdd(Now(), 1, Days), Type: "Dayshift"},
 {Date: DateAdd(Now(), 2, Days), Type: "Dayshift"},
 {Date: DateAdd(Now(), 3, Days), Type: "Rest"},
 {Date: DateAdd(Now(), 4, Days), Type: "Dayshift"},
 {Date: DateAdd(Now(), 5, Days), Type: "Dayshift"},
 {Date: DateAdd(Now(), 6, Days), Type: "Rest"}
)

Gallery Data source:

With(
 //Initialise a day to count from for the first group of day Types
 {FirstCountDay: DateAdd(Min(colWorkDays, Date), -1, Days)},
 AddColumns(
 colWorkDays As ThisDay,
 "Consecutive Days",
 With(
 //Find the previous day with a different Type
 {
 PrevDiffType: 
 First(
 SortByColumns(
 Filter(colWorkDays As B, B.Type <> ThisDay.Type && B.Date < ThisDay.Date),
 "Date", Descending
 )
 ).Date
 },
 //Calculate difference between current day and the latest day of a different Type (always zero if Resting)
 If(
 ThisDay.Type = "Rest",
 0,
 0 + DateDiff(Coalesce(PrevDiffType, FirstCountDay), ThisDay.Date, Days)
 )
 )

 /*
 //===== uncomment this block for debugging =====
 ,
 "PrevDiffType", First(
 SortByColumns(
 Filter(colWorkDays As B, B.Type <> ThisDay.Type && B.Date < ThisDay.Date),
 "Date", Descending
 )
 ).Date,
 "FirstCountDay", Min(colWorkDays, Date)
 */
 )
)

In its current form, all dates between the minimum and maximum must be present in the collection or else you will get calculation issues (because I'm using DateDiff, not counting occurrences of "Dayshift" Type).