Again, thanks for your continued support on this. Delegation does seem to be a headache (especially when using SharePoint as the data source).
The results still aren't returning expected values. I have attached a screen grab so you can see.
The original query returns 1742 'completions' - which, looking at the source data, is the expected value:
Round(
Sum(
Filter(
DailyDataAggregated,
Len(DatePicker_Start_8.SelectedDate) = 0 || DateCollected > DateAdd(
DatePicker_Start_8.SelectedDate,
-1,
Days
),
Len(DatePicker_End_8.SelectedDate) = 0 || DateCollected < DateAdd(
DatePicker_End_8.SelectedDate,
1,
Days
),
StartsWith(
'OUC Label',
DropdownOUC_9.Selected.Value
)
),
Completions
),
0
)
When using the local 'With' query the result is a lot lower - (I added weekday 1,2,3,4,5,6,7 to reproduce the same filter' = 460 completions
With(
{
wLast:
DateAdd(
DatePicker_Start_8.SelectedDate,
-1,
Days
),
wNext:
DateAdd(
DatePicker_Start_8.SelectedDate,
1,
Days
)
},
With(
{
wList:
Filter(
DailyDataAggregated,
(
Value(DatePicker_Start_8.SelectedDate) < 1 ||
DateCollected > wLast
) &&
(
Value(DatePicker_End_8.SelectedDate) < 1 ||
DateCollected < wNext
) &&
StartsWith(
'OUC Label',
DropdownOUC_9.Selected.Value
)
)
},
Round(
Sum(
Filter(
wList,
Weekday(DateCollected) in [1,2,3,4,5,6,7]
),
Completions
),
0
)
)
)
On further investigation (looking at the source data) I can see that the 'With' query is only returning results from the 7th July (the start date of the date picker). No other dates are being summed.
