Forum Discussion
Count rows when a given date is between two other dates
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.
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
MaggieCommunity 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.
1 Reply
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
MaggieCommunity 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.