Count occurrences between 2 dates on a calendar table

[pedanticpad]

Hi,

I have a table with StartDate and EndDate.  I need to count the number of occurances between the StartDate and EndDate on a calendar table along the lines of the below.  Any help would be greatly appreciated.

StartDate	EndDate		CalendarDate	Count
01/12/2019	03/12/2019		01/12/2019	3
01/12/2019	04/12/2019		02/12/2019	4
01/12/2019	04/12/2019		03/12/2019	3
02/12/2019	08/12/2019		04/12/2019	1
			05/12/2019	1
			06/12/2019	1
			07/12/2019	1
			08/12/2019	0

Thanks

[Icey · Accepted solution]

Hi @pedanticpad ,

Try this:

Count Column = 
CALCULATE (
    COUNTROWS ( 'Table' ) + 0,
    FILTER (
        'Table',
        'Table'[StartDate] <= EARLIER ( 'Calendar'[Date] )
            && 'Table'[EndDate] > EARLIER ( 'Calendar'[Date] )
    )
)

Best Regards,

Icey

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

[pedanticpad]

Thanks so much! That works perfectly

[Icey]

Hi @pedanticpad ,

Try this:

1. Create relationships between the two tables.

2. Create a measure.

Count = 
COUNT ( 'Table'[StartDate] )
    + CALCULATE (
        COUNT ( 'Table'[EndDate] ),
        USERELATIONSHIP ( 'Calendar'[Date], 'Table'[EndDate] )
    ) + 0

Best Regards,

Icey

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

[pedanticpad]

Thanks Icey, Is there any way to use that to create a table that I can add calculated columns to rather than a visual?

Also, is it possible to ammend your formula to pull in criteria?

[Tahreem24]

Or you can use below DAX:
Column = DATEDIFF(Table1[Dates1], Table1[Dates2], DAY)

[pedanticpad]

Thanks for the reply. Those 2 solutions work to give me the number of days between the two dates, but not the number of instances between 2 dates. 

What I need is to be able to say on each day of the year we had x occupancy by using the StartDate and EndDate.

If it makes it any easier I was able to achieve it in MS Access using the below;

SELECT Import_CalendarDate.CalendarDate, Count(ID) AS TotalOccupancy
FROM Import_CalendarDate LEFT JOIN RoomOccupancy ON (Import_CalendarDate.CalendarDate>=RoomOccupancy.StartDate) AND (Import_CalendarDate.CalendarDate<RoomOccupancy.EndDate)
WHERE Import_CalendarDate.CalendarDate<date()
GROUP BY CalendarDate
ORDER BY Import_CalendarDate.CalendarDate;

[Tahreem24]

What I got from your point is to count some occurances between 2 dates.

Measure =
CALCULATE (
SUM ( ValueTable[Value] ),
FILTER (
ValueTable,
ValueTable[Date] >= MIN( PeriodTable[StartDate] )
&& ValueTable[Date] <= MAX ( PeriodTable[EndDate] )
)
)

Please give Kudos and accept this as a solution if it helps you.

[pedanticpad]

Hi,
&nbsp;
Tahreem,&nbsp; I can't get your formula to work at all.&nbsp; Icey, your formula is pulling in the wrong values.
&nbsp;
&nbsp;
Just to calrify the aim;
&nbsp;
Think of it as a hotel (field name, etc changes to make more sense with a hotel example).&nbsp; I have a list of Admission Dates, Discharge Dates and Room Numbers.&nbsp; I need to see the nightly occupancy for each day, so that if 5 rooms were occupied last night (regardless of their admission date) I would see an occupancy of 5.&nbsp; To add to that not all rooms will be occupied overnight (admit and discharge date the same) so I only need to count it if the admission date and discharge date are not the same.
&nbsp;
Thanks.

[Tahreem24]

Give a try using below calculated column :
Days Column =
SWITCH (
TRUE (),
'Table'[start_date] < 'Table'[end_date], DATEDIFF ( 'Table'[start_date], 'Table'[end_date], DAY ),
'Table'[start_date] > 'Table'[end_date], DATEDIFF ( 'Table'[end_date], 'Table'[start_date], DAY ) * -1,
0
)

Don't forget to hit Thumbs up and mark it as a solution if it helps you.