cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

How to get extract active event given the time interval dynamically?

I have a table that looks something like this:

+----------+-------------------------------+-------------------------------+
| E_ID         | StartTime                               | EndTime                                |
+----------+-------------------------------+-------------------------------+
| 18 | 2013-04-10 16:33:11.735342100 | 2013-04-11 17:16:47.976164100 |
| 23 | 2013-04-11 16:33:46.575337300 | 2013-04-11 18:10:08.428443900 |
| 19 | 2013-04-11 17:17:04.033083300 | 2013-04-11 18:10:13.907757900 |
| 16 | 2013-04-11 18:10:24.293352100 | 2013-04-11 18:45:17.754240800 |
| 24 | 2013-04-11 18:11:20.278558900 | 2013-04-11 18:45:00.435247300 |
| 17 | 2013-04-15 19:42:22.549026700 | 2013-04-15 23:54:33.389964300 |
| 30 | 2013-04-16 17:42:24.588807700 | 2013-04-16 17:42:41.077751300 |
| 14 | 2013-04-19 16:51:22.699240800 | 2013-04-19 18:39:03.167468100 |
| 31 | 2013-04-19 18:30:56.891621300 | 2013-04-19 19:42:50.418640200 |
| 10 | 2013-04-20 16:07:07.327879000 | 2013-04-20 22:17:17.783053600 |
| 25 | 2013-04-22 18:40:03.193588300 | 2013-04-22 18:45:39.692862500 |
| 35 | 2013-04-22 22:37:44.611643600 | 2013-04-23 22:58:59.614665600 |
| 36 | 2013-04-27 10:35:15.924381800 | 2013-04-27 17:06:34.757135400 |
| 38 | 2013-04-01 10:56:37.887895200 | 2013-04-01 18:48:45.523163200 |
| 03 | 2013-04-05 09:50:59.676090700 | 2013-04-05 23:10:59.997149500 |
| 33 | 2013-04-08 20:04:35.081422100 | 2013-04-09 22:42:06.391661100 |
| 29 | 2013-04-13 17:04:50.256379600 | 2013-04-13 23:02:24.006316400 |
| 34 | 2013-04-14 05:37:12.330001300 | 2013-04-14 18:32:15.545483100 |
| 04 | 2013-04-15 03:28:57.726491800 | 2013-04-15 23:33:33.332273600 |
| 44 | 2013-04-17 01:42:28.476431100 | 2013-04-17 21:44:21.853923500 |
+----------+-------------------------------+-------------------------------+

 

 

 

I need to find events that are active during certain time period e.g. 12-14, but I want to do this dynamically, using a range slider. But there are some issues as from above data you can see that some events starts from previous date and were active during my desired interval. How to compare those.

 

My desired output looks like this:

 

 

+----------+-------------------------------+-------------------------------+
| E_ID         | StartTime                               | EndTime                                 |
+----------+-------------------------------+-------------------------------+
| 18 | 2013-04-10 16:33:11.735342100 | 2013-04-11 17:16:47.976164100 |
| 35 | 2013-04-22 22:37:44.611643600 | 2013-04-23 22:58:59.614665600 |
| 36 | 2013-04-27 10:35:15.924381800 | 2013-04-27 17:06:34.757135400 |
| 38 | 2013-04-01 10:56:37.887895200 | 2013-04-01 18:48:45.523163200 |
| 03 | 2013-04-05 09:50:59.676090700 | 2013-04-05 23:10:59.997149500 |
| 33 | 2013-04-08 20:04:35.081422100 | 2013-04-09 22:42:06.391661100 |
| 34 | 2013-04-14 05:37:12.330001300 | 2013-04-14 18:32:15.545483100 |
| 04 | 2013-04-15 03:28:57.726491800 | 2013-04-15 23:33:33.332273600 |
| 44 | 2013-04-17 01:42:28.476431100 | 2013-04-17 21:44:21.853923500 |
+----------+-------------------------------+-------------------------------+

Basically, i need a fliter, that can dynamically get values and extract the datetime values that fit the condition. I want to show these values in visual table and some other avg calculations with cards etc.

5 REPLIES 5
Highlighted
Resolver IV
Resolver IV

Re: How to get extract active event given the time interval dynamically?

Hi @saadnaveed ,

 

I think something like this can work:

Select = 
var X1 = min('Time'[Time])
var X2 = Max('Time'[Time])

var Y1 = MIN('Table'[StartTime])
var Y2 = MAX('Table'[EndTime])

var D1 = Date( YEAR(Y1), Month(Y1), DAY(Y1)) + (X1 / 24)
var D2 = Date( YEAR(Y2), Month(Y2), DAY(Y2)) + (X2 / 24)

return
IF (Y1<D2 && Y2>D1, "Selected")
It selects the desired output IDs
2020-03-31 22_08_04-Window.png
 
Hope this makes any sense 🙂
 
Jan if this is a solution for you, don't forget to mark it as such. thanks
 
 
Highlighted
Frequent Visitor

Re: How to get extract active event given the time interval dynamically?

can you share .pbix with me or share some details of what you have done

Highlighted
Resolver IV
Resolver IV

Re: How to get extract active event given the time interval dynamically?

I hope this works (via WeTransfer)

 

.pbix file (until 7-Apr-2020) 

 

Compared to the screenshot there is not too much more happening in the file 🙂

 

 

Highlighted
Frequent Visitor

Re: How to get extract active event given the time interval dynamically?

honestly its very helpful and have solved my issue, but i was looking for some way that not only table gets updated, but also interact with other visuals on the same page e.g a card with count of Events, their cost(avg, total) etc. can you help with that. I did found a post here with predefined timeslots but i want to do it dynamically.

Highlighted
Resolver IV
Resolver IV

Re: How to get extract active event given the time interval dynamically?

If you it to be truely dynamic, then (as far as you know)  you need to copy to the logic to select E_IDs in any of the measures you want to use in a visual. You can't use calculated tables since they will always produce the same result (unless you change the definition ofcourse)

 

For instance, something like this to calculated the selected E_ID rows. 

 

RowsSelected = 

var X1 = min('Time'[Time])
var X2 = max('Time'[Time])

var result =

GROUPBY(
    GroupBy (
        'Table',
        'Table'[E_ID],
        'Table'[StartTime],
        'Table'[EndTime],
        "FilterStart", MINX( CURRENTGROUP(),
         var Y1 = 'Table'[StartTime]
                 var D1 = Date( YEAR(Y1), Month(Y1), DAY(Y1)) + (X1 / 24)
                 Return D1
                 ),

        "FilterEnd", MINX( CURRENTGROUP(),
         var Y2 = 'Table'[EndTime]
                 var D2 = Date( YEAR(Y2), Month(Y2), DAY(Y2)) + (X2 / 24)
                 Return D2
                 )
        )
,

    'Table'[E_ID], "COL",SUMX( CURRENTGROUP(),
if (
AND([FilterStart]<'Table'[EndTime],
[FilterEnd]>'Table'[StartTime])
, 1 -- Selected E_ID

//
// --- YOUR CALCULATION to calculate intermediate results (instead of the ,1)
//

, BLANK())

)
)

return
countrows(result)

 

  Hope this makes any sense.

 

jan 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors