Count number of rows between Week Start Date & Week End Date

[Adam_Harris]

Hi,

I have a table with column Date (dd/mm/yyyy), Week Start Date , Week End date.

How do I count number of rows between Week Start Date & Week End Date just for year 2020.

Exp:-

Week Range                           Total Rows

______________                        _______________

6/1/2020 - 12/1/2020                    128

13/1/2020 - 19/1/2020                   122

Any ideas on how I could write a formula to achieve this?

[vivran22]

Hello @Adam_Harris,

How you are defining the Week Range? Is it dynamic or static?

Regards,

Vivek

[Adam_Harris]

Week Range

Week Start Date = Date[Date] - WEEKDAY(Date[Date],2) + 1

Week End Date = Date[Date] - WEEKDAY(Date[Date],2) + 7

in a dynamic

[Gordonlilj]

Hi,

Perhaps you could try something like

Measure = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Date]>= MIN('Table'[Week Start Date])
            && 'Table'[Date] <= MAX ('Table'[Week End Date])
))

[Adam_Harris]

Its give me result :

Week Start Date      Week End Date      Total Rows
17/2/2020                  23/2/2020               1
17/2/2020                  23/2/2020               1
17/2/2020                  23/2/2020               1
17/2/2020                  23/2/2020               1
17/2/2020                  23/2/2020               1
17/2/2020                  23/2/2020               1
17/2/2020                  23/2/2020               1
17/2/2020                  23/2/2020               1
17/2/2020                  23/2/2020               1
10/2/2020                  16/2/2020               1
10/2/2020                  16/2/2020               1
10/2/2020                  16/2/2020               1

But i'm expecting like this:

Week Start Date           Week End Date        Total Rows
17/2/2020                    23/2/2020                  18
10/2/2020                    16/2/2020                  21

[V-lianl-msft · Accepted solution]

Hi @Adam_Harris ,

You can try to use the DAX below to create a calculated column:

Column = CALCULATE(COUNTROWS(test),FILTER(test,EARLIER(test[Week Start Date])=test[Week Start Date]&&EARLIER(test[Week End Date])=test[Week End Date]))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.