Running count by hour of the day

[Waimea]

Hi, I have crossposted a similar thread at Re: "Running Count by Hour of the Day" thread and no solution has been found.

I am looking at #15 from the original thread, which can be found Running Count by Hour of the Day [SOLVED].

I am trying to understand exactly what this DAX formula does.

Active Customers:=CALCULATE (
        COUNTROWS ( Customers ),
        FILTER (
            Customers,
            Customers[Exit Time] >= MIN( Times[Time] ) &&
            Customers[Arrival Time] <= MAX ( Times[End Time] )
        )
    )

I would be glad if someone can help me to understand this DAX formula bit for bit?

Se #15 in original thread for Times[Time] table.
.

 

[GraH]

What is it you do not understand? Basically it filters a table called "Customers" where the time is between an interval, comparing Exit/Arrival time to a Time table in which I suspect Time - End Time is an hourly interval. With this filter applied on the Customer Table the number of records/rows are counted. Calculate is required to have the filter applied on the measure "countrows" and it will overwrite the "normal" filter context of your PowerPivot.

 

[Waimea]

Hi GraH,

thank you very much for your reply.

Does it show me how many customers where in my store in a particular hourly interval?

Say a customer enters my store at 10:15 and leaves 11:10.

What does this DAX formula tell me about that visit?

 

[GraH]

Looking at the formula I'd say this visit in counted at the interval "11-12". Since Exit time is greater the 11 and Arrival time is smaller then 12. Now the thread says running count... But I'm not sure about that as I'm very rusty when it comes down to DAX as I hardly/never need it at work. And you need to keep the practise to see through the formulas so to speak: it's a mental thing, right. Let's wait on others to enlighten you.
PS: I don't have access to the files on that forum, otherwise we could simply look "under the hood".

 

[Waimea]

Hi GraH,

thank you for your reply. I don't think that I can attach files on this forum and I don't have any good means to share a file.

I want to count the number of customers that enters my store for a given time span, per 15 minute periods would be kick-***!

All help is very much appreciated!

 

[GraH]

Questions:
Do you really have a lot of data?
How well do you understand the concepts of DAX and PowerPivot?

Alternative might be simpler, using a flooring/ceiling formula and a standard pivot.

Book1
	D	E	F	G	H	I	J	K	L
3	Time	Count	15Min RoundUp	15Min RoundDown		Hours	15Min RoundUp	15Min RoundDown	Sum of Count
4	12/04/2020 11:30	1	12/04/2020 11:45	12/04/2020 11:30		0	:00	:45	1
5	12/04/2020 1:07	1	12/04/2020 1:15	12/04/2020 1:00			:15	:00	2
6	12/04/2020 22:33	1	12/04/2020 22:45	12/04/2020 22:30		0 Total			3
7	12/04/2020 11:21	1	12/04/2020 11:30	12/04/2020 11:15		1	:15	:00	2
8	12/04/2020 14:26	1	12/04/2020 14:30	12/04/2020 14:15			:45	:30	2
9	12/04/2020 23:14	1	12/04/2020 23:15	12/04/2020 23:00		1 Total			4
10	12/04/2020 9:54	1	12/04/2020 10:00	12/04/2020 9:45		2	:00	:45	1
11	12/04/2020 7:27	1	12/04/2020 7:30	12/04/2020 7:15			:15	:00	3
12	12/04/2020 21:28	1	12/04/2020 21:30	12/04/2020 21:15			:30	:15	1
13	12/04/2020 13:46	1	12/04/2020 14:00	12/04/2020 13:45			:45	:30	1
14	12/04/2020 0:12	1	12/04/2020 0:15	12/04/2020 0:00		2 Total			6
15	12/04/2020 8:43	1	12/04/2020 8:45	12/04/2020 8:30		3	:00	:45	1
16	12/04/2020 10:29	1	12/04/2020 10:30	12/04/2020 10:15			:45	:30	1
17	12/04/2020 6:25	1	12/04/2020 6:30	12/04/2020 6:15		3 Total			2
18	12/04/2020 21:54	1	12/04/2020 22:00	12/04/2020 21:45		4	:00	:45	1
19	12/04/2020 4:41	1	12/04/2020 4:45	12/04/2020 4:30			:45	:30	1
20	12/04/2020 23:45	1	13/04/2020 0:00	12/04/2020 23:45		4 Total			2
21	12/04/2020 2:09	1	12/04/2020 2:15	12/04/2020 2:00		5	:45	:30	1
22	12/04/2020 2:08	1	12/04/2020 2:15	12/04/2020 2:00		5 Total			1
23	12/04/2020 21:13	1	12/04/2020 21:15	12/04/2020 21:00		6	:00	:45	1
24	12/04/2020 21:36	1	12/04/2020 21:45	12/04/2020 21:30			:30	:15	3
25	12/04/2020 22:40	1	12/04/2020 22:45	12/04/2020 22:30		6 Total			4
26	12/04/2020 12:24	1	12/04/2020 12:30	12/04/2020 12:15		7	:00	:45	1
27	12/04/2020 15:03	1	12/04/2020 15:15	12/04/2020 15:00			:30	:15	1
28	12/04/2020 2:45	1	12/04/2020 3:00	12/04/2020 2:45			:45	:30	2
29	12/04/2020 2:42	1	12/04/2020 2:45	12/04/2020 2:30		7 Total			4
30	12/04/2020 9:27	1	12/04/2020 9:30	12/04/2020 9:15		8	:15	:00	1
31	12/04/2020 0:07	1	12/04/2020 0:15	12/04/2020 0:00			:45	:30	1
32	12/04/2020 13:29	1	12/04/2020 13:30	12/04/2020 13:15		8 Total			2
33	12/04/2020 6:25	1	12/04/2020 6:30	12/04/2020 6:15		9	:00	:45	1
34	12/04/2020 21:03	1	12/04/2020 21:15	12/04/2020 21:00			:30	:15	1
35	12/04/2020 18:52	1	12/04/2020 19:00	12/04/2020 18:45			:45	:30	1
36	12/04/2020 15:09	1	12/04/2020 15:15	12/04/2020 15:00		9 Total			3
37	12/04/2020 18:33	1	12/04/2020 18:45	12/04/2020 18:30		10	:00	:45	4
38	12/04/2020 11:08	1	12/04/2020 11:15	12/04/2020 11:00			:15	:00	2
39	12/04/2020 12:51	1	12/04/2020 13:00	12/04/2020 12:45			:30	:15	1
40	12/04/2020 21:58	1	12/04/2020 22:00	12/04/2020 21:45			:45	:30	2
41	12/04/2020 23:14	1	12/04/2020 23:15	12/04/2020 23:00		10 Total			9
42	12/04/2020 19:27	1	12/04/2020 19:30	12/04/2020 19:15		11	:00	:45	2
43	12/04/2020 11:35	1	12/04/2020 11:45	12/04/2020 11:30			:15	:00	1
44	12/04/2020 19:20	1	12/04/2020 19:30	12/04/2020 19:15			:30	:15	2
45	12/04/2020 19:10	1	12/04/2020 19:15	12/04/2020 19:00			:45	:30	4
46	12/04/2020 10:51	1	12/04/2020 11:00	12/04/2020 10:45		11 Total			9
47	12/04/2020 10:04	1	12/04/2020 10:15	12/04/2020 10:00		12	:00	:45	1
48	12/04/2020 7:33	1	12/04/2020 7:45	12/04/2020 7:30			:30	:15	2
49	12/04/2020 11:25	1	12/04/2020 11:30	12/04/2020 11:15			:45	:30	1
50	12/04/2020 10:56	1	12/04/2020 11:00	12/04/2020 10:45		12 Total			4
51	12/04/2020 4:46	1	12/04/2020 5:00	12/04/2020 4:45		13	:00	:45	1
52	12/04/2020 21:39	1	12/04/2020 21:45	12/04/2020 21:30			:15	:00	1
53	12/04/2020 11:49	1	12/04/2020 12:00	12/04/2020 11:45			:30	:15	1
54	12/04/2020 16:25	1	12/04/2020 16:30	12/04/2020 16:15		13 Total			3
55	12/04/2020 3:39	1	12/04/2020 3:45	12/04/2020 3:30		14	:00	:45	1
56	12/04/2020 1:31	1	12/04/2020 1:45	12/04/2020 1:30			:30	:15	1
57	12/04/2020 6:26	1	12/04/2020 6:30	12/04/2020 6:15		14 Total			2
58	12/04/2020 19:43	1	12/04/2020 19:45	12/04/2020 19:30		15	:00	:45	1
59	12/04/2020 11:47	1	12/04/2020 12:00	12/04/2020 11:45			:15	:00	2
60	12/04/2020 21:34	1	12/04/2020 21:45	12/04/2020 21:30		15 Total			3
61	12/04/2020 18:20	1	12/04/2020 18:30	12/04/2020 18:15		16	:15	:00	1
62	12/04/2020 11:43	1	12/04/2020 11:45	12/04/2020 11:30			:30	:15	3
63	12/04/2020 14:55	1	12/04/2020 15:00	12/04/2020 14:45			:45	:30	1
64	12/04/2020 19:35	1	12/04/2020 19:45	12/04/2020 19:30		16 Total			5
65	12/04/2020 6:49	1	12/04/2020 7:00	12/04/2020 6:45		17	:00	:45	1
66	12/04/2020 10:47	1	12/04/2020 11:00	12/04/2020 10:45		17 Total			1
67	12/04/2020 16:27	1	12/04/2020 16:30	12/04/2020 16:15		18	:00	:45	1
68	12/04/2020 2:08	1	12/04/2020 2:15	12/04/2020 2:00			:30	:15	1
69	12/04/2020 1:42	1	12/04/2020 1:45	12/04/2020 1:30			:45	:30	1
70	12/04/2020 12:28	1	12/04/2020 12:30	12/04/2020 12:15		18 Total			3
71	12/04/2020 2:24	1	12/04/2020 2:30	12/04/2020 2:15		19	:15	:00	2
72	12/04/2020 0:48	1	12/04/2020 1:00	12/04/2020 0:45			:30	:15	2
73	12/04/2020 1:06	1	12/04/2020 1:15	12/04/2020 1:00			:45	:30	2
74	12/04/2020 10:39	1	12/04/2020 10:45	12/04/2020 10:30		19 Total			6
75	12/04/2020 19:00	1	12/04/2020 19:15	12/04/2020 19:00		20	:00	:45	1
76	12/04/2020 10:39	1	12/04/2020 10:45	12/04/2020 10:30		20 Total			1
77	12/04/2020 7:57	1	12/04/2020 8:00	12/04/2020 7:45		21	:00	:45	2
78	12/04/2020 9:44	1	12/04/2020 9:45	12/04/2020 9:30			:15	:00	2
79	12/04/2020 13:00	1	12/04/2020 13:15	12/04/2020 13:00			:30	:15	2
80	12/04/2020 8:01	1	12/04/2020 8:15	12/04/2020 8:00			:45	:30	3
81	12/04/2020 23:12	1	12/04/2020 23:15	12/04/2020 23:00		21 Total			9
82	12/04/2020 5:41	1	12/04/2020 5:45	12/04/2020 5:30		22	:45	:30	3
83	12/04/2020 7:34	1	12/04/2020 7:45	12/04/2020 7:30		22 Total			3
84	12/04/2020 17:52	1	12/04/2020 18:00	12/04/2020 17:45		23	:00	:45	1
85	12/04/2020 21:15	1	12/04/2020 21:30	12/04/2020 21:15			:15	:00	3
86	12/04/2020 15:53	1	12/04/2020 16:00	12/04/2020 15:45		23 Total			4
87	12/04/2020 10:55	1	12/04/2020 11:00	12/04/2020 10:45		Grand Total			93
88	12/04/2020 11:38	1	12/04/2020 11:45	12/04/2020 11:30
89	12/04/2020 16:10	1	12/04/2020 16:15	12/04/2020 16:00
90	12/04/2020 3:54	1	12/04/2020 4:00	12/04/2020 3:45
91	12/04/2020 22:35	1	12/04/2020 22:45	12/04/2020 22:30
92	12/04/2020 10:09	1	12/04/2020 10:15	12/04/2020 10:00
93	12/04/2020 20:52	1	12/04/2020 21:00	12/04/2020 20:45
94	12/04/2020 16:15	1	12/04/2020 16:30	12/04/2020 16:15
95	12/04/2020 12:35	1	12/04/2020 12:45	12/04/2020 12:30
96	12/04/2020 16:31	1	12/04/2020 16:45	12/04/2020 16:30
Sheet1

Cell Formulas
Range	Formula
F4:F96	F4	=CEILING.MATH(D4,TIME(0,15,0))
G4:G96	G4	=FLOOR.MATH(D4,TIME(0,15,0))

 

[Waimea]

Hi GraH,

thank you for your time and your reply!

I have lots of data and that's why I turned towards PowerPivot and DAX, I have +1 million rows.

I am currently reading DAX Formulas for PowerPivot 2nd Edition and I have a data model and I am using the DAX formula in the top of the thread.

However, I don't understand completely what the DAX formula does and that makes me weary and that is why I started this thread!

 

[GraH]

It is a good book. If I recall correctly, Rob talks about the greatest formula in the world in one of the chapters, and that's a nested calculate/filter combo .
To test the measure, you can make a pivot with all dimensions required (customer, day, enter and exit times) and filter the pivot (tabular form) as you think the measure works. Then count the records and so if that adds up to the same total with the measure.