PowerApps - CountRows based on the week number from a date

[Wilder1626]

Hi everyone.

I have a Dynamics 365 table date column called: "Completed_date". 

What i would like to do is to count the number of rows where that completed_date column = Week number 13.

So far, i've tried below without any luck. I even tried without formating the Completed_date column, but still same result.

How can i make that count?

Thanks again for your help.

CountRows(
    Filter(
        QA_Table,
        RoundDown(
   (RoundUp(
      Text(Completed_Date,"[$-en-US]mm/dd/yyyy") - Date(
          Year(Text(Completed_Date,"[$-en-US]mm/dd/yyyy")  - Weekday(Text(Completed_Date,"[$-en-US]mm/dd/yyyy")  - 1) + 4),
          1,
          3
      ),
      0
   ) + Weekday(
      Date(
         Year(Text(Completed_Date,"[$-en-US]mm/dd/yyyy")  - Weekday(Text(Completed_Date,"[$-en-US]mm/dd/yyyy")  - 1) + 4),
         1,
         3
      )
   ) + 5) / 7,
   0
)​="13"
    )
)

[Ashwin7104]

Hi @Wilder1626 ,

I tried replicating the same with SharePoint as the data source and was able to achieve the following result using the formula below.

CountRows(
    Filter(
        Companies_1,
If(Len(Text(RoundDown((RoundUp (
Modified - Date(
Year(Modified - Weekday(Modified - 1) + 4),
1,
3
),
0
) + Weekday(
Date(
Year(Modified - Weekday(Modified - 1) + 4),
1,
3
)
) + 5) / 7,
0
)))=1,"0"&RoundDown((RoundUp (
Modified - Date(
Year(Modified - Weekday(Modified - 1) + 4),
1,
3
),
0
) + Weekday(
Date(
Year(Modified - Weekday(Modified - 1) + 4),
1,
3
)
) + 5) / 7,
0
),RoundDown((RoundUp (
Modified - Date(
Year(Modified - Weekday(Modified - 1) + 4),
1,
3
),
0
) + Weekday(
Date(
Year(Modified - Weekday(Modified - 1) + 4),
1,
3
)
) + 5) / 7,
0
)) = "10"
    )
)

Companies 1 is the name of my SharePoint list, and Modified is the filter column, and 10 as the week. Having said that, I do not believe the date time column can be delegated. You'll need to use a delegable function and column type to delegate queries/filters on that list. You could do this with a number or text.

[Anonymous]

@Wilder1626 

Place this on a label

With(
    {
        vAddCols: AddColumns(
            colDates,
            "WeekNum",
            With(
                {
                    v1stMonth: Month(CompDate) = 1,
                    v1stDay: Day(CompDate) = 1,
                    vDateDiff: With(
                        {
                            vYear: Year(CompDate),
                            vDate: CompDate
                        },
                        DateDiff(
                            Date(
                                vYear,
                                1,
                                7 - Weekday(
                                    Date(
                                        vYear,
                                        1,
                                        1
                                    )
                                ) + 1
                            ),
                            vDate,
                            Days
                        )
                    )
                },
                If(
                    v1stDay && v1stMonth,
                    1,
                    RoundUp(
                        vDateDiff / 7,
                        0
                    ) + 1
                )
            )
        )
    },
    CountRows(
        Filter(
            vAddCols,
            WeekNum = Value(TextInput6.Text)
        )
    )
)

My references vs Yours:

- colDates = QA_Table

- CompDate = Completed_Date

- Value(TextInput6.Text) = 13. I used a textinput field to add a dynamic element but you can just replace this with 13

As a side note, I don't use Dynamics but I'd look at add a Week Number field into your dataset to make this easy. Not sure if there's a function that does this but surely it better than all this code ...

Also, unsure of you dataset size but CountRows isn't delegable to CDS / Dataverse (which I think is what Dynamics uses?) so you will only be able to count up to 2,000 records ... but that maybe OK for your situation?

[Anonymous]

@Ashwin7104 

Whoops sorry mate, I was working on this as well then forgot to refresh before posting.

[Ashwin7104]

@Anonymous - no worries. We're all attempting to solve issues 🙂

[Wilder1626]

Hi Ashwin7104 and  Eelman

Thanks for your help. I've tried both solutions, just to see the 2 results, and I'm seeing 2 different results when i query from my dbase. Manually counted, i should get a result of 98 for week 13.

Also, i need to say that it can have a lot more than over 25 000 records in that Data Base.

With Ashwin7104 solution, i have a count of 24 if I enter "13" and also if i use "Dropdown3.Selected.Result" that contains 13.

With Eelman solution, i have a count of 67 if I enter "13" and also if i use "Dropdown3.Selected.Result" that contains 13.

I'm trying to understand why it would be different but no answer yet.

Eelman, even if it gives me a result at the end, I'm having a portion of the code in error:

Ashwin7104, i'm having a Delegation warning. This part "Filter" of this formula might not work correctly on large data sets. Could that be the reason why it does not give me the correct count?

[Anonymous · Accepted solution]

@Wilder1626 

The reason for the count differences are to do with delegation and data row limits. You won't get what you need using countrows or AddColumns with 25,000 records. Not sure why my code has an error, I suspect a missing comma or bracket somewhere?

One way to solve this would be to parse into a Filter the StartDate and EndDate of Week 13.

ie

CountRows(

     Filter(

           QA_Table, Completed_Date >= StartDate, Completed_Date <= EndDate 

     )

)

This would show a delegation warning but would still count up to 2000 Week 13 records for you. Hence, if your data wouldn't get more than 2000 Week 13 records then this will work for you.

With a bit of messing around you could re-jig my code to get these days for you or I can look at it tomorrow?

[Wilder1626]

Thanks for the extra info. I will look try a few tings.

Just an FYI,  while pulling week 13 details, i realize that date in Completed_Date column are week 12.

2 samples:

Would you know why? That may be the reason why the count would not be what it should somehow?

For me, a week starts on Sunday and end on Saturday.

[Anonymous]

@Wilder1626 

The Week Number depend solely on your definition of when they start each year. I used Excel's WEEKNUM() function to check my code and from all the checks I did my code worked fine.

Looking at a 2021 calendar, the 1/1/2021 is a Friday meaning that Week 1 is Fri/Sat only. Week 2 then starts on 3/1/2021 and this is how I shaped my code.

My code looks for the first Sunday then uses that date to add 7 days to and not just starting on the 1/1.

Those dates you have listed are in Week 13 of the year, using my code and definition but if you're definition is different then you'll need to define that then re-write the code to suit.

[Wilder1626]

It makes sense what you are saying. What I now realize is that it counts records from 2020 and 2021. But not all the records that it should. So this is where the 2000 rows of records limitation affect the result. I need to prevent the multi years records to be in conflict. That becomes a little harder now. I may have to sort Completed_Date Descending first.