Filter and Sum by weekday?

[lowdmt]

Hi,

I’m working on a label to filter and sum data (from a SharePoint list) between 2 dates selected from start and end date pickers. I have the below code which achieves this.

Round(
    Sum(
        Filter(
            DataAggregated,
            Len(DatePicker_Start.SelectedDate) = 0 || DateCollected > DateAdd(
                DatePicker_Start.SelectedDate,
                -1,
                Days
            ),
            Len(DatePicker_End.SelectedDate) = 0 || DateCollected < DateAdd(
                DatePicker_End.SelectedDate,
                1,
                Days
            ),
            StartsWith(
                'Label',
                DropdownLabel.Selected.Value
            )
        ),
        ColumnToSum
    ),
    0
)

I want to sum the same column but only using business days (weekdays 2,3,4,5,6). How do I add a filter to only sum if the DateCollected column is a weekday?

I’m aware the query isn’t delegable but this isn’t an issue for me as I’ll be under the 2000 row limit.

[WarrenBelz]

Hi @lowdmt ,

Try this

Round(
    Sum(
        Filter(
            DataAggregated,
            Len(DatePicker_Start.SelectedDate) = 0 || DateCollected > DateAdd(
                DatePicker_Start.SelectedDate,
                -1,
                Days
            ),
            Len(DatePicker_End.SelectedDate) = 0 || DateCollected < DateAdd(
                DatePicker_End.SelectedDate,
                1,
                Days
            ),
            StartsWith(
                'Label',
                DropdownLabel.Selected.Value
            ),
            Weekday(DateCollected) in [2,3,4,5,6]
        ),
        ColumnToSum
    ),
    0
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[lowdmt]

Hi @WarrenBelz 

This just returns a 0.

Interestingly, if I remove the datepicker filters the query starts returning results.

Round(
    Sum(
        Filter(
            DataAggregated,
            StartsWith(
                'Label',
                DropdownLabel.Selected.Value
            ),
            Weekday(DateCollected) in [2,3,4,5,6]
        ),
        ColumnToSum
    ),
    0
)

But obviously I need to keep the datepicker functionality. 

Thanks for your help on this

[WarrenBelz]

Hi @lowdmt ,

That is simply an additional filter - I suspect the issue is Delegation - try this

With(
   {
      wLast:
	  DateAdd(
         DatePicker_Start.SelectedDate,
         -1,
         Days
      ),
      wNext:
      DateAdd(
         DatePicker_Start.SelectedDate,
         1,
         Days
      )
   },
   With(
      {
         wList:
         Filter(
            DataAggregated,
            (
               Value(DatePicker_Start.SelectedDate) < 1 || 
               DateCollected > wLast 
            ) &&
            (
               Value(DatePicker_End.SelectedDate) < 1 || 
               DateCollected < wNext
            ) &&
            StartsWith(
               'Label',
               DropdownLabel.Selected.Value
            )
         )
      },  
      Round(
         Sum(
            Filter(
               wList,
               Weekday(DateCollected) in [2,3,4,5,6]
            ),
            ColumnToSum
         ),
         0
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[lowdmt]

There seems to be an error in the formula (see attached). 

[WarrenBelz · Accepted solution]

@lowdmt ,

That code was free-typed in NotePad as syntax guidance for you and I assumed you would have looked for an error before coming back. There were two typos (see if you can spot them - although one did not cause and error) now fixed. Also could you please post any code in Text also (saves retyping and risking more errors). I have now tested this and it should produce the required result in a label providing your data filters find appropriate records ) - it did on my test list)

With(
   {
      wLast:
	  DateAdd(
         DatePicker_Start.SelectedDate,
         -1,
         Days
      ),
      wNext:
      DateAdd(
         DatePicker_Start.SelectedDate,
         1,
         Days
      )
   },
   With(
      {
         wList:
         Filter(
            DailyDataAggregated,
            (
               Value(DatePicker_Start.SelectedDate) < 1 || 
               DateCollected > wLast 
            ) &&
            (
               Value(DatePicker_End.SelectedDate) < 1 || 
               DateCollected < wNext
            ) &&
            StartsWith(
               'OEC Label',
               DropdownOEC.Selected.Value
            )
         )
      },  
      Round(
         Sum(
            Filter(
               wList,
               Weekday(DateCollected) in [2,3,4,5,6]
            ),
            ID
         ),
         0
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[WarrenBelz]

Hi @lowdmt ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[lowdmt]

Hi @WarrenBelz 

Thanks for your help on this. I wasn't familiar with the 'With'.

The formula works fine although I have an issue with the 'in' for the weekday being non delegable...

I tried to design the query to reduce the results returned to less than 2000 (by using the date picker and 'StartsWith') to allow me to do a SUM calculation. The data being 'summed' will never go over 2000 rows. The actual SharePoint data is 60,000 rows in size but will always be reduced to less than 2000 by the filter..

 StartsWith(
               'OEC Label',
               DropdownOEC.Selected.Value
            )

The inclusion of the 'in' operator in the Weekday filter seems to be causing an issue... it will only sum the first 2000 rows of data not the selected 2000 rows. 

I need to filter the returned 'OEC Label' results (max 200 rows) and then filter again by Weekday. 

Hope that makes sense?

[WarrenBelz]

@lowdmt ,

That is what I have done - the top With statement is "local" and simply returns two dates to make the second one Delegable, so as long as the output of that one returns less than 2,000 rows, the last Filter will work fine. Also Sum() is not Delegable, so needs to go on the bottom item (also processed locally).

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[lowdmt]

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.