LIST DATE TIME/ PowerQuery / Dax

[RK9009]

Greetings Everyone, 

I am trying to solve a situation, not very successful at it until now. Can we get a list of date and time in power query or with dax. 

below is the example for what I am trying to achive. 

personId	startdate	starttime	enddate	endtime	location
1	12/13/2020	10:19 PM	12/15/2020	3:08 PM	A
1	12/15/2020	3:08 PM	12/16/2020	10:42 AM	B
1	12/16/2020	10:42 AM	12/23/2020	4:00 PM	B

I am trying get a list of date and time for multiple personID to calculate the distinct count at a location based on a given date time. i.e Everyday as of 5:00 am 

to put this in a simple context I want to count this personID at one location as of 5 am between the start and end date. 

Any help is much appriciated and thank you so much as always. 🙂

Dax Help

@PowerQueryFTW 

@dax

[amitchandak]

new column =
var _1 = date(year([enddate]),month([enddate]),day([enddate])) + time(5,0,0)
return
if( (_1 <=[enddate] && _1 >= [starttime]) ||  (_1-1 <=[enddate] && _1-1 >= [starttime]) ,"Yes", "No")

[amitchandak · Accepted solution]

@RK9009 , Try a new column in Dax like

new column =
var _1 = date(year([enddate]),month([enddate]),day([enddate])) + time(5,0,0)
return
if( (_1 <=[enddate] && _1 >= [starttime]) && (_1-1 <=[enddate] && _1-1 >= [starttime]) ,"Yes", "No")