Modified StartDateTime and EndDateTime dynamically

[vincentllx]

Hi !

I'm kinda new to power Bi, and I'd like to know if i can do the following in Power Bi.

Note that I'm using DirectQuery to get my data. I can't create link between tables since all my data is integrated in a single table.

My situation:

I have events that start and end on a certain period like :

EventId	StartDateTime	EndDateTime	DurationSec
abc	11/08/2021 10:40:21 AM	11/08/2021 3:44:23 PM	18242
def	11/08/2021 11:55:10 PM	12/08/2021 3:40:21 AM	13511
ijk	10/08/2021 10:40:21 PM	11/08/2021 10:40:21 AM	43200

I'm trying to create calculated columns to calculate the duration on a specific interval used as a filter.

Now I want to filter all these events on the 11th and to have their duration in sec as follow:

EventId	StartDateTime2	EndDateTime2	DurationSec2
abc	11/08/2021 10:40:21 AM	11/08/2021 3:44:23 PM	18242
def	11/08/2021 11:55:10 PM	12/08/2021 12:00:00 AM	290
ijk	11/08/2021 12:00:00 AM	11/08/2021 10:40:21 AM	38421

Now lets admit I want to filter all these events on the 12th and to have their duration in sec as follow:

EventId	StartDateTime2	EndDateTime2	DurationSec2
abc	11/08/2021 10:40:21 AM	11/08/2021 3:44:23 PM	0
def	12/08/202112:00:00 AM	12/08/2021 3:40:21 AM	13221
ijk	10/08/2021 10:40:21 PM	11/08/2021 10:40:21 AM	0

If I could have some hint to think this problem it would be higly appreciated ! Thanks 🙂 

[amitchandak]

@vincentllx , Logic for the last table is not clear, few dates are changed, what is the logic behind that