Trying to get count - Point in time based on 2 datetime fields

[amit_maniyar]

I have a scenario based on given datase

I am trying to count valid Ids at point in time say Yesterday, today, today -7 days - on hourly line graph

Test Cases

Yesterday (13 Feb 2022 at 3 pm) , how many records were valid ?  (any id which has start date prior to that and end date later to that will be counted as valid

other info
Same applies for today - 7 days counts - on hourly basis

Any record with null enddate is valid Starting from Startdatetime

ID	Start Datetime	End Datetime
1	2/14/23 11:01
2	2/14/23 10:58
3	2/14/23 10:55
4	2/14/23 10:53
5	2/14/23 10:52
6	2/14/23 10:49
7	2/14/23 10:46
8	2/14/23 10:43
9	2/14/23 10:41
10	2/14/23 10:37
11	2/14/23 10:35
12	2/14/23 10:31
13	2/14/23 10:30
14	2/14/23 10:24
15	2/14/23 10:21
16	2/14/23 10:20
17	2/14/23 10:15
18	2/14/23 10:15
19	2/14/23 10:08
20	2/14/23 10:05
21	2/14/23 10:00
22	2/14/23 9:58
23	2/14/23 9:53
24	2/14/23 9:51
25	2/14/23 9:49	2/14/23 11:01
26	2/14/23 9:44	2/14/23 11:00
27	2/14/23 9:44	2/14/23 10:59
28	2/14/23 9:38
29	2/14/23 9:36
30	2/14/23 9:32
31	2/14/23 9:27	2/14/23 10:35
32	2/14/23 9:27	2/14/23 10:59
33	2/14/23 9:22
34	2/14/23 9:08
35	2/14/23 9:07
36	2/14/23 9:03	2/14/23 10:32
37	2/14/23 9:01
38	2/14/23 8:57
39	2/14/23 8:57	2/14/23 10:34
40	2/14/23 8:53	2/14/23 10:33
41	2/14/23 8:50
42	2/14/23 8:47
43	2/14/23 8:41
44	2/14/23 8:38
45	2/14/23 8:34	2/14/23 10:31
46	2/14/23 8:21	2/14/23 10:35
47	2/14/23 8:19	2/14/23 10:34
48	2/14/23 7:51	2/14/23 10:36
49	2/14/23 7:44	2/14/23 10:33
50	2/14/23 7:35
51	2/14/23 7:30
52	2/14/23 7:29	2/14/23 11:00
53	2/14/23 7:18	2/14/23 10:58
54	2/14/23 7:02
55	2/14/23 6:58	2/14/23 10:58
56	2/14/23 6:54	2/14/23 10:35
57	2/14/23 6:51	2/14/23 10:36
58	2/14/23 6:18
59	2/14/23 6:15	2/14/23 11:00
60	2/14/23 6:11	2/14/23 7:17
61	2/14/23 5:35	2/14/23 11:00
62	2/14/23 5:31	2/14/23 10:36
63	2/14/23 5:28	2/14/23 8:38
64	2/14/23 4:52	2/14/23 5:43
65	2/14/23 3:15	2/14/23 7:25
66	2/14/23 2:59	2/14/23 7:26
67	2/14/23 2:54
68	2/14/23 2:51	2/14/23 6:01
69	2/14/23 2:40	2/14/23 6:02
70	2/14/23 1:35	2/14/23 7:25
71	2/14/23 1:32	2/14/23 7:26
72	2/14/23 1:23	2/14/23 5:07
73	2/14/23 1:21	2/14/23 5:59
74	2/14/23 0:49	2/14/23 6:00
75	2/14/23 0:45	2/14/23 7:51
76	2/14/23 0:38	2/14/23 7:22
77	2/14/23 0:36	2/14/23 4:37
78	2/14/23 0:32	2/14/23 7:24
79	2/14/23 0:32

[v-jianboli-msft · Accepted solution]

Hi @amit_maniyar ,

Please try:

First create a new table:

Use [Time] as x-axis

Then apply the measure to the line chart:

_Today = 
var _a = TODAY()+MAX('Time'[Time])
return COUNTX(FILTER('Table',[Start Datetime]<=_a&&OR([End Datetime]>=_a,ISBLANK([End Datetime]))),[ID])

_Today-7 = 
var _a = TODAY()-7+MAX('Time'[Time])
return COUNTX(FILTER('Table',[Start Datetime]<=_a&&OR([End Datetime]>=_a,ISBLANK([End Datetime]))),[ID])

_Yesterday = 
var _a = TODAY()-1+MAX('Time'[Time])
return COUNTX(FILTER('Table',[Start Datetime]<=_a&&OR([End Datetime]>=_a,ISBLANK([End Datetime]))),[ID])

Final output:

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

Hi @amit_maniyar ,

Sorry I misunderstood you before, you don't need to add measure to the visual level filter if you only want to limit today's display, you just need to change today's measure:

_Today = 
var _a = TODAY()+MAX('Time'[Time])
var _b =  COUNTX(FILTER('Table',[Start Datetime]<=_a&&OR([End Datetime]>=_a,ISBLANK([End Datetime]))),[ID])
return IF(MAX('Time'[Time])<=TIME(HOUR(NOW()),0,0),_b)

Best Regards,

Jianbo Li

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

[v-jianboli-msft]

Hi @amit_maniyar ,

Please try to add this mueasure to the visual level filter:

Measure = IF(MAX('Time'[Time])<=TIME(HOUR(NOW()),0,0),1,0)

Final output:

Now is 2023/2/16 9:25 am

Best Regards,

Jianbo Li

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

[amit_maniyar]

Hi @v-jianboli-msft ,

I dont want to show the streight line for today's counts. I tried to appy this to visual filter, but the Yesterday and today - 7 - rest of the hours data also wont show up.

Any help is appreciated! thanks so much !

Amit

[amit_maniyar]

Hi @v-jianboli-msft , 

You are genius. it worked really great for my other dataset as well. 

Although I have a question about Today's line as per attached the image below.

I am wondering if we can somehow restrict today's line would be upto now (current hour).

Right now, its a constant number going across all the hours.

Appreciate your help!!

Thanks, 

Amit

[amit_maniyar]

Thanks @v-jianboli-msft.

Logic appears to be very clear to me. 

I am surprised to know what can I do if I have a dimtime table already linked with my startdatetime field. 

right now, I created a separate time table (as you did, thanks). which dont need a relationship.

[v-jianboli-msft · Accepted solution]

Hi @amit_maniyar ,

Please try:

First create a new table:

Use [Time] as x-axis

Then apply the measure to the line chart:

_Today = 
var _a = TODAY()+MAX('Time'[Time])
return COUNTX(FILTER('Table',[Start Datetime]<=_a&&OR([End Datetime]>=_a,ISBLANK([End Datetime]))),[ID])

_Today-7 = 
var _a = TODAY()-7+MAX('Time'[Time])
return COUNTX(FILTER('Table',[Start Datetime]<=_a&&OR([End Datetime]>=_a,ISBLANK([End Datetime]))),[ID])

_Yesterday = 
var _a = TODAY()-1+MAX('Time'[Time])
return COUNTX(FILTER('Table',[Start Datetime]<=_a&&OR([End Datetime]>=_a,ISBLANK([End Datetime]))),[ID])

Final output:

Best Regards,

Jianbo Li

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