Counting occurrences between dates

[JB2020]

Hi

I currently have a table which contains a list of customers with timestamps for when they enter or leave a department. I would like to be able to summarize in some selected period the average and range of the number of people in a department at a certain time. For example, in January the average number of people in department A at 12:00 is 5 and it ranged between 2 and 7 . The way I have achieved this currently is by creating a list of each time period (in this case each hour) using power query and then calculating the number of people in that department at each of these times using a calculated column in power pivot.

The formula I am using for this is:
=Calculate(
DistinctCount([CustomerID]),
Filter(
[Department] = "A" &&
[Time In] <= [Time] &&
[Time Out] > [Time])
)

This produces the correct result, but is really slow to calculate. I am looking for help on whether there is a way to speed up this formula, or whether I am just approaching it wrong in the first place?

I am only using Power Pivot, not power BI, and I have provided a couple of example tables below.

Thanks

CustomerID	Department	Time In	Time Out
1	A	1/1/2020 09:50	2/1/2020 10:20
2	A	1/1/2020 11:30	1/1/2020 12:05

Time	Number of customers in department A (calculated column)
1/1/2020 10:00	1
1/1/2020 11:00	0
1/1/2020 12:00	1

 

[anvg]

Hi.
Let your top table is Visits, and the bottom table is Times.
Let Department A have 100 customer visits. I didn't quite understand why customer 1 has an exit time of 2/1/2020 10:10 - did he spend the night in department A? Suppose a customer visit is always done on only one day. The visit table has data for 365 days of one year.
Let the time table have 8 hours for each day and describe 365 days in one year.
Then we get 100 * 365 * 365 * 8 = 106,580,000 views to calculate the number of customers for each hour.
To increase speed, let's add a Dates column to the Visits and Times tables and create a Calendar table. Let's link Visits and Times to the Calendar. Then we can speed up the calculation of the number of clients per hour for the Times table by 365 times.

=Var curDate = CALCULATETABLE(VALUES('Calendar'[Date]); 'Visits')
Return CALCULATE(DISTINCTCOUNT('Visits'[CustomerID]); curDate; FILTER('Visits'; 'Visits'[Department] = "A" && 'Visits'[Time In] <= 'Times'[Time] && 'Times'[Time] <= 'Visits'[Time Out]))

Since the view in the Visits table will only be performed for one day.
Regards,

 

[JB2020]

Hi.
Let your top table is Visits, and the bottom table is Times.
Let Department A have 100 customer visits. I didn't quite understand why customer 1 has an exit time of 2/1/2020 10:10 - did he spend the night in department A? Suppose a customer visit is always done on only one day. The visit table has data for 365 days of one year.
Let the time table have 8 hours for each day and describe 365 days in one year.
Then we get 100 * 365 * 365 * 8 = 106,580,000 views to calculate the number of customers for each hour.
To increase speed, let's add a Dates column to the Visits and Times tables and create a Calendar table. Let's link Visits and Times to the Calendar. Then we can speed up the calculation of the number of clients per hour for the Times table by 365 times.

=Var curDate = CALCULATETABLE(VALUES('Calendar'[Date]); 'Visits')
Return CALCULATE(DISTINCTCOUNT('Visits'[CustomerID]); curDate; FILTER('Visits'; 'Visits'[Department] = "A" && 'Visits'[Time In] <= 'Times'[Time] && 'Times'[Time] <= 'Visits'[Time Out]))

Since the view in the Visits table will only be performed for one day.
Regards,

Thanks for your reply, this is really helpful. Apologies, that 2 is supposed to be a 1, although it is a 24 hour service therefore someone could arrive at 1/1/20 11:55 and leave at 2/1/20 00:05 for example. Given that, would you recommend any updates to the formula you provided? Thanks again.

 

[anvg]

I upload my demo Power BI Desktop file.
But you can speed up your own code significantly by using a following expression.

Column = Var needVisitRows = Filter('Visits',
    'Visits'[Department] = "A" &&
    'Visits'[Time In] <= 'Times'[Time] &&
    'Visits'[Time Out] > 'Times'[Time]
)
Var stats = GROUPBY(needVisitRows, 'Visits'[CustomerID])
Return COUNTROWS(stats)

Regards,

 

[JB2020]

I upload my demo Power BI Desktop file.
But you can speed up your own code significantly by using a following expression.

Column = Var needVisitRows = Filter('Visits',
    'Visits'[Department] = "A" &&
    'Visits'[Time In] <= 'Times'[Time] &&
    'Visits'[Time Out] > 'Times'[Time]
)
Var stats = GROUPBY(needVisitRows, 'Visits'[CustomerID])
Return COUNTROWS(stats)

Regards,

I will try this out today, thanks again for your help.