Count of X below 48hrs

[shankyy7227]

Hello All,

I have a table as shown below and i am trying to calculate count of FName below 48 hrs.

FNAME	LNAME	MNAME	Entry Date	Entry Time	Exit Date	Exit Time
AB	CD	X	6/6/2018	20:00:00 PM	6/9/2018	21:00:00 PM
BC	EF	Y	6/6/2018	19:00:00 PM	6/10/2018	19:00:00 PM
AB	CD	X	5/30/2018	20:00:00 PM	5/31/2018	22:00:00 PM
IH	PQ	Z	5/30/2018	24:00:00 PM	6/1/2018	17:00:00 PM
IM	PY	C	5/30/2018	18:00:00 PM	6/2/2018	00:00.0
			5/30/2018	18:00:00 PM	6/3/2018	18:00:00 PM
ME	EE	B	5/30/2018	18:00:00 PM	6/4/2018	18:00:00 PM
AB	CD	X	5/30/2018	18:00:00 PM	6/5/2018	18:00:00 PM
BC	EF	Y	6/6/2018	18:00:00 PM	6/9/2018	18:00:00 PM

Please help me.

Thanks

[Hardik]

Column = IF(DATEDIFF(Table2[Entry Date],Table2[Exit Date],DAY)<2,1,0)

I hope this calculated column can be used a filter to meet your requirement

[Phil_Seamark]

Hi @shankyy7227

Aren't all the rows under 48 hours?  I'm guessing you mean the values in the [Time] column?

[shankyy7227]

There may be in above sample data but they are not in real time data

[Phil_Seamark]

So will the value in the [Time] column be something like '50:00.0' if it represented 50 hours - and this row should be excluded from the COUNT calculation?

[shankyy7227]

Updated the above table, Please go through it once again.

Thanks 🙂

[Phil_Seamark · Accepted solution]

Hi @shankyy7227

I added the following calculated column to your table

Hours between Entry and Exit = 
VAR EntryDate = 'Table1'[Entry Time]
VAR EntryTimeError = IF(EntryDate="24:00:00 PM",1,0)
VAR CombinedEntryDateTime = 'Table1'[Entry Date] + TIMEVALUE(IF(EntryTimeError,"00:00:00",EntryDate)) + IF(EntryTimeError,1,0)

VAR ExitDate = 'Table1'[Exit Date]
VAR ExitCombinedDateTime = ExitDate + TIMEVALUE('Table1'[Exit Time])

RETURN DATEDIFF(CombinedEntryDateTime,ExitCombinedDateTime,HOUR)

Which then allowed me to create the following calculated measure to count the rows.

Measure = CALCULATE(COUNTROWS('Table1'),'Table1'[Hours between Entry and Exit]<= 48)

I have attached a PBIX file to this post

[shankyy7227]

I am trying calculate total number of people staying past 48 hours.

We need to combine Date and Time column here