cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lowdmt
Helper II
Helper II

Filter and Sum by weekday?

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.

1 ACCEPTED SOLUTION

Accepted Solutions

@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

 

 

View solution in original post

11 REPLIES 11
WarrenBelz
Super User
Super User

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
Helper II
Helper II

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

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

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

@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
Super User
Super User

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

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?

 

@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

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. 

 

SumofCompletions.PNG

 

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. 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,442)