I have a scenario based on given dataseI am trying to count valid Ids at point in time say Yesterday, today, today -7 days - on hourly line graph Test CasesYesterday (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 validother infoSame applies for today - 7 days counts - on hourly basisAny record with null enddate is valid Starting from Startdatetime IDStart DatetimeEnd Datetime12/14/23 11:01 22/14/23 10:58 32/14/23 10:55 42/14/23 10:53 52/14/23 10:52 62/14/23 10:49 72/14/23 10:46 82/14/23 10:43 92/14/23 10:41 102/14/23 10:37 112/14/23 10:35 122/14/23 10:31 132/14/23 10:30 142/14/23 10:24 152/14/23 10:21 162/14/23 10:20 172/14/23 10:15 182/14/23 10:15 192/14/23 10:08 202/14/23 10:05 212/14/23 10:00 222/14/23 9:58 232/14/23 9:53 242/14/23 9:51 252/14/23 9:492/14/23 11:01262/14/23 9:442/14/23 11:00272/14/23 9:442/14/23 10:59282/14/23 9:38 292/14/23 9:36 302/14/23 9:32 312/14/23 9:272/14/23 10:35322/14/23 9:272/14/23 10:59332/14/23 9:22 342/14/23 9:08 352/14/23 9:07 362/14/23 9:032/14/23 10:32372/14/23 9:01 382/14/23 8:57 392/14/23 8:572/14/23 10:34402/14/23 8:532/14/23 10:33412/14/23 8:50 422/14/23 8:47 432/14/23 8:41 442/14/23 8:38 452/14/23 8:342/14/23 10:31462/14/23 8:212/14/23 10:35472/14/23 8:192/14/23 10:34482/14/23 7:512/14/23 10:36492/14/23 7:442/14/23 10:33502/14/23 7:35 512/14/23 7:30 522/14/23 7:292/14/23 11:00532/14/23 7:182/14/23 10:58542/14/23 7:02 552/14/23 6:582/14/23 10:58562/14/23 6:542/14/23 10:35572/14/23 6:512/14/23 10:36582/14/23 6:18 592/14/23 6:152/14/23 11:00602/14/23 6:112/14/23 7:17612/14/23 5:352/14/23 11:00622/14/23 5:312/14/23 10:36632/14/23 5:282/14/23 8:38642/14/23 4:522/14/23 5:43652/14/23 3:152/14/23 7:25662/14/23 2:592/14/23 7:26672/14/23 2:54 682/14/23 2:512/14/23 6:01692/14/23 2:402/14/23 6:02702/14/23 1:352/14/23 7:25712/14/23 1:322/14/23 7:26722/14/23 1:232/14/23 5:07732/14/23 1:212/14/23 5:59742/14/23 0:492/14/23 6:00752/14/23 0:452/14/23 7:51762/14/23 0:382/14/23 7:22772/14/23 0:362/14/23 4:37782/14/23 0:322/14/23 7:24792/14/23 0:32

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.

Forum Discussion

amit_maniyar

Helper I

3 years ago

Solved

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

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

Need Help

v-jianboli-msft

3 years ago

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.

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

v-jianboli-msft
3 years ago
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.

6 Replies

v-jianboli-msft

Community Support

3 years ago

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.

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

v-jianboli-msft
Community Support
3 years ago
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.
Trying to get count - Point in time based on 2 datetime fields.pbix54 KB
- amit_maniyar
  Helper I
  3 years ago
  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
v-jianboli-msft
Community Support
3 years ago
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.
amit_maniyar
Helper I
3 years ago
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.
amit_maniyar
Helper I
3 years ago
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

Forum Discussion

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

6 Replies

Related content

Get Time from DateTime field

FORMAT DATETIME Field spark sql notebook cell

MashupException.Error while trying to load incremental data by DateTime field

Converting Epoch Datetime into regular datetime format

Error in Power Query combining overlapping start end datetime fields