Count rows when a given date is between two other dates

[Wybo_H]

Hi,

I've got a date table and a table B with a date period.

Now for each date in the date table I want to count the rows from table B where the date from the date table is between the startDate and the EndDate.

The result should be something like below.

i've searched for a solution, but could not find it.

[v-juanli-msft · Accepted solution]

Hi @Wybo_H 

main table

id	start	end
1	1/1/2019	1/1/2019
2	1/1/2019	1/2/2019
3	1/2/2019	1/5/2019
4	1/3/2019	1/5/2019
5	1/4/2019	1/6/2019
6	1/5/2019	1/6/2019
7	1/6/2019	1/7/2019
8	1/8/2019	1/9/2019
9	1/9/2019	1/9/2019

Calendar table contains dates above

Create a table

Table = FILTER(CROSSJOIN('calendar','main table'),[Date]>=[start]&&[Date]<=[end])

Then add a column in this table

count = CALCULATE(COUNT('Table'[id]),ALLEXCEPT('Table','Table'[Date]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.