topic Re: Contious events time cutoffs in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Contious-events-time-cutoffs/m-p/3153863#M113061 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="148838" data-lia-user-login="amitchandak" class="lia-mention lia-mention-user">amitchandak</a>&nbsp;,</P><P>&nbsp;</P><P>Sorry, I have been very busy and many thank for your reply.</P><P>I am not quite sure if I can upload a file here.<BR /><BR /></P><P>Here is a sample table below (Not enough), but it will give you an idea. There are 7 activities done by a machine A, and each activity is recorded in second. We are asked to provide the activities happend in 24 hours window - their total hours/min/sec for each activity. Let's say from 24/03/2023 06:00 AM to 25/03/2023 06:00 AM the next day.</P><P>&nbsp;</P><P>Now if the activity 7 starts 25/03/2023 05:55:20 AM to 25/03/2023 06:14:10 AM, we have to make sure that the cut-off for 24 hours is 25/03/2023 06:00:00 AM, which means activity 7 would be from&nbsp;25/03/2023 05:55:20 AM to&nbsp;25/03/2023&nbsp;06:00:00 AM for this reporting of 24 hrs. The result would look like (in chart) with 24 hrs, distributted with different activities total in 24 hrs. from 6 AM to 6 AM the next day.</P><P>&nbsp;</P><P>24 hrs&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;</P><TABLE border="1"><TBODY><TR><TD>act1 = 2 hrs</TD><TD>act4 = 3 hrs</TD><TD>act3 = 7hrs</TD><TD>act5 = 1hrs</TD><TD>act2 = 2.5 hrs</TD><TD>act6 = 1.2 hrs</TD><TD>...</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>machine</TD><TD>start_date</TD><TD>end_date</TD><TD>activity</TD><TD>running_time (sec)</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:35:20</TD><TD>24/01/2023 14:35:24</TD><TD>Activity 1</TD><TD>4</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:35:24</TD><TD>24/01/2023 14:35:51</TD><TD>Activity 2</TD><TD>27</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:35:51</TD><TD>24/01/2023 14:35:54</TD><TD>Activity 2</TD><TD>3</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:35:54</TD><TD>24/01/2023 14:36:04</TD><TD>Activity 2</TD><TD>10</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:36:04</TD><TD>24/01/2023 14:40:35</TD><TD>Activity 2</TD><TD>271</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:40:35</TD><TD>24/01/2023 14:40:36</TD><TD>Activity 1</TD><TD>1</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:40:36</TD><TD>24/01/2023 14:41:10</TD><TD>Activity 2</TD><TD>34</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:41:10</TD><TD>24/01/2023 14:46:00</TD><TD>Activity 2</TD><TD>290</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:46:00</TD><TD>24/01/2023 14:46:02</TD><TD>Activity 1</TD><TD>2</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:46:02</TD><TD>24/01/2023 14:46:50</TD><TD>Activity 2</TD><TD>48</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:46:50</TD><TD>24/01/2023 14:46:52</TD><TD>Activity 2</TD><TD>2</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:46:52</TD><TD>24/01/2023 14:47:19</TD><TD>Activity 2</TD><TD>27</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:47:19</TD><TD>24/01/2023 14:50:51</TD><TD>Activity 2</TD><TD>212</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:50:51</TD><TD>24/01/2023 14:50:52</TD><TD>Activity 1</TD><TD>1</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:50:52</TD><TD>24/01/2023 14:51:58</TD><TD>Activity 2</TD><TD>66</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:51:58</TD><TD>24/01/2023 14:56:19</TD><TD>Activity 2</TD><TD>261</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:56:19</TD><TD>24/01/2023 14:56:24</TD><TD>Activity 1</TD><TD>5</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:56:24</TD><TD>24/01/2023 14:59:05</TD><TD>Activity 2</TD><TD>161</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:59:05</TD><TD>24/01/2023 15:02:17</TD><TD>Activity 2</TD><TD>192</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:02:17</TD><TD>24/01/2023 15:02:20</TD><TD>Activity 1</TD><TD>3</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:02:20</TD><TD>24/01/2023 15:04:14</TD><TD>Activity 2</TD><TD>114</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:04:14</TD><TD>24/01/2023 15:04:20</TD><TD>Activity 2</TD><TD>6</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:04:20</TD><TD>24/01/2023 15:05:53</TD><TD>Activity 2</TD><TD>93</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:05:53</TD><TD>24/01/2023 15:09:27</TD><TD>Activity 2</TD><TD>214</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:09:27</TD><TD>24/01/2023 15:09:29</TD><TD>Activity 1</TD><TD>2</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:09:29</TD><TD>24/01/2023 15:10:25</TD><TD>Activity 2</TD><TD>56</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:10:25</TD><TD>24/01/2023 15:10:35</TD><TD>Activity 2</TD><TD>10</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:10:35</TD><TD>24/01/2023 15:12:22</TD><TD>Activity 2</TD><TD>107</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:12:22</TD><TD>24/01/2023 15:15:26</TD><TD>Activity 2</TD><TD>184</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:15:26</TD><TD>24/01/2023 15:15:27</TD><TD>Activity 1</TD><TD>1</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:15:27</TD><TD>24/01/2023 15:19:23</TD><TD>Activity 2</TD><TD>236</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:19:23</TD><TD>24/01/2023 15:22:40</TD><TD>Activity 2</TD><TD>197</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:22:40</TD><TD>24/01/2023 15:22:42</TD><TD>Activity 1</TD><TD>2</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:22:42</TD><TD>24/01/2023 15:26:59</TD><TD>Activity 2</TD><TD>257</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:26:59</TD><TD>24/01/2023 15:30:22</TD><TD>Activity 2</TD><TD>203</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:30:22</TD><TD>24/01/2023 15:30:27</TD><TD>Activity 1</TD><TD>5</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:30:27</TD><TD>24/01/2023 15:31:33</TD><TD>Activity 2</TD><TD>66</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:31:33</TD><TD>24/01/2023 15:35:12</TD><TD>Activity 2</TD><TD>219</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:35:12</TD><TD>24/01/2023 15:35:16</TD><TD>Activity 1</TD><TD>4</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:35:16</TD><TD>24/01/2023 15:36:00</TD><TD>Activity 2</TD><TD>44</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:36:00</TD><TD>24/01/2023 15:40:50</TD><TD>Activity 1</TD><TD>290</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:40:50</TD><TD>24/01/2023 15:42:53</TD><TD>Activity 2</TD><TD>123</TD></TR></TBODY></TABLE> Sat, 25 Mar 2023 13:57:25 GMT hirwa 2023-03-25T13:57:25Z Contious events time cutoffs https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Contious-events-time-cutoffs/m-p/3118651#M110494 <P>Hello guys,</P><P>&nbsp;</P><P>I have a question and I am not sure if it has been asked before somewhere else, but please give it a try. I have a continous event. For example, the cutoff reporting time is midnight - to make it easier. Then there is an event that start 23:40:50 and finishes 00:34:40. I was able to write a code that can report this event from 23:40:50 and finishes 23:59:59 for yesterday and works fine. Now the question to you guys, how do I bring the left time frame from 00:00:00 to 00:40:50 of this event to the next day. Suppose that a sensor is creating different events no stop and we want to report only the events from 00:00:00 to 23:59:59.</P><P><BR />Thank you in advance!</P><P>&nbsp;</P><P>H.</P> Wed, 08 Mar 2023 11:23:59 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Contious-events-time-cutoffs/m-p/3118651#M110494 hirwa 2023-03-08T11:23:59Z Re: Contious events time cutoffs https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Contious-events-time-cutoffs/m-p/3120395#M110639 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="526062" data-lia-user-login="hirwa" class="lia-mention lia-mention-user">hirwa</a> , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.</P> <P>&nbsp;</P> <P>refer if this can help</P> <P>Continuous streak : <A href="https://youtu.be/GdMcwvdwr0o" target="_blank">https://youtu.be/GdMcwvdwr0o</A></P> Thu, 09 Mar 2023 02:57:39 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Contious-events-time-cutoffs/m-p/3120395#M110639 amitchandak 2023-03-09T02:57:39Z Re: Contious events time cutoffs https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Contious-events-time-cutoffs/m-p/3153863#M113061 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="148838" data-lia-user-login="amitchandak" class="lia-mention lia-mention-user">amitchandak</a>&nbsp;,</P><P>&nbsp;</P><P>Sorry, I have been very busy and many thank for your reply.</P><P>I am not quite sure if I can upload a file here.<BR /><BR /></P><P>Here is a sample table below (Not enough), but it will give you an idea. There are 7 activities done by a machine A, and each activity is recorded in second. We are asked to provide the activities happend in 24 hours window - their total hours/min/sec for each activity. Let's say from 24/03/2023 06:00 AM to 25/03/2023 06:00 AM the next day.</P><P>&nbsp;</P><P>Now if the activity 7 starts 25/03/2023 05:55:20 AM to 25/03/2023 06:14:10 AM, we have to make sure that the cut-off for 24 hours is 25/03/2023 06:00:00 AM, which means activity 7 would be from&nbsp;25/03/2023 05:55:20 AM to&nbsp;25/03/2023&nbsp;06:00:00 AM for this reporting of 24 hrs. The result would look like (in chart) with 24 hrs, distributted with different activities total in 24 hrs. from 6 AM to 6 AM the next day.</P><P>&nbsp;</P><P>24 hrs&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;</P><TABLE border="1"><TBODY><TR><TD>act1 = 2 hrs</TD><TD>act4 = 3 hrs</TD><TD>act3 = 7hrs</TD><TD>act5 = 1hrs</TD><TD>act2 = 2.5 hrs</TD><TD>act6 = 1.2 hrs</TD><TD>...</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>machine</TD><TD>start_date</TD><TD>end_date</TD><TD>activity</TD><TD>running_time (sec)</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:35:20</TD><TD>24/01/2023 14:35:24</TD><TD>Activity 1</TD><TD>4</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:35:24</TD><TD>24/01/2023 14:35:51</TD><TD>Activity 2</TD><TD>27</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:35:51</TD><TD>24/01/2023 14:35:54</TD><TD>Activity 2</TD><TD>3</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:35:54</TD><TD>24/01/2023 14:36:04</TD><TD>Activity 2</TD><TD>10</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:36:04</TD><TD>24/01/2023 14:40:35</TD><TD>Activity 2</TD><TD>271</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:40:35</TD><TD>24/01/2023 14:40:36</TD><TD>Activity 1</TD><TD>1</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:40:36</TD><TD>24/01/2023 14:41:10</TD><TD>Activity 2</TD><TD>34</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:41:10</TD><TD>24/01/2023 14:46:00</TD><TD>Activity 2</TD><TD>290</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:46:00</TD><TD>24/01/2023 14:46:02</TD><TD>Activity 1</TD><TD>2</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:46:02</TD><TD>24/01/2023 14:46:50</TD><TD>Activity 2</TD><TD>48</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:46:50</TD><TD>24/01/2023 14:46:52</TD><TD>Activity 2</TD><TD>2</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:46:52</TD><TD>24/01/2023 14:47:19</TD><TD>Activity 2</TD><TD>27</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:47:19</TD><TD>24/01/2023 14:50:51</TD><TD>Activity 2</TD><TD>212</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:50:51</TD><TD>24/01/2023 14:50:52</TD><TD>Activity 1</TD><TD>1</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:50:52</TD><TD>24/01/2023 14:51:58</TD><TD>Activity 2</TD><TD>66</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:51:58</TD><TD>24/01/2023 14:56:19</TD><TD>Activity 2</TD><TD>261</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:56:19</TD><TD>24/01/2023 14:56:24</TD><TD>Activity 1</TD><TD>5</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:56:24</TD><TD>24/01/2023 14:59:05</TD><TD>Activity 2</TD><TD>161</TD></TR><TR><TD>A</TD><TD>24/01/2023 14:59:05</TD><TD>24/01/2023 15:02:17</TD><TD>Activity 2</TD><TD>192</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:02:17</TD><TD>24/01/2023 15:02:20</TD><TD>Activity 1</TD><TD>3</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:02:20</TD><TD>24/01/2023 15:04:14</TD><TD>Activity 2</TD><TD>114</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:04:14</TD><TD>24/01/2023 15:04:20</TD><TD>Activity 2</TD><TD>6</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:04:20</TD><TD>24/01/2023 15:05:53</TD><TD>Activity 2</TD><TD>93</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:05:53</TD><TD>24/01/2023 15:09:27</TD><TD>Activity 2</TD><TD>214</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:09:27</TD><TD>24/01/2023 15:09:29</TD><TD>Activity 1</TD><TD>2</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:09:29</TD><TD>24/01/2023 15:10:25</TD><TD>Activity 2</TD><TD>56</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:10:25</TD><TD>24/01/2023 15:10:35</TD><TD>Activity 2</TD><TD>10</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:10:35</TD><TD>24/01/2023 15:12:22</TD><TD>Activity 2</TD><TD>107</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:12:22</TD><TD>24/01/2023 15:15:26</TD><TD>Activity 2</TD><TD>184</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:15:26</TD><TD>24/01/2023 15:15:27</TD><TD>Activity 1</TD><TD>1</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:15:27</TD><TD>24/01/2023 15:19:23</TD><TD>Activity 2</TD><TD>236</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:19:23</TD><TD>24/01/2023 15:22:40</TD><TD>Activity 2</TD><TD>197</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:22:40</TD><TD>24/01/2023 15:22:42</TD><TD>Activity 1</TD><TD>2</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:22:42</TD><TD>24/01/2023 15:26:59</TD><TD>Activity 2</TD><TD>257</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:26:59</TD><TD>24/01/2023 15:30:22</TD><TD>Activity 2</TD><TD>203</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:30:22</TD><TD>24/01/2023 15:30:27</TD><TD>Activity 1</TD><TD>5</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:30:27</TD><TD>24/01/2023 15:31:33</TD><TD>Activity 2</TD><TD>66</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:31:33</TD><TD>24/01/2023 15:35:12</TD><TD>Activity 2</TD><TD>219</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:35:12</TD><TD>24/01/2023 15:35:16</TD><TD>Activity 1</TD><TD>4</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:35:16</TD><TD>24/01/2023 15:36:00</TD><TD>Activity 2</TD><TD>44</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:36:00</TD><TD>24/01/2023 15:40:50</TD><TD>Activity 1</TD><TD>290</TD></TR><TR><TD>A</TD><TD>24/01/2023 15:40:50</TD><TD>24/01/2023 15:42:53</TD><TD>Activity 2</TD><TD>123</TD></TR></TBODY></TABLE> Sat, 25 Mar 2023 13:57:25 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Contious-events-time-cutoffs/m-p/3153863#M113061 hirwa 2023-03-25T13:57:25Z