Countrows help

[dblackwood72]

Hello,

I have a dax measure that counts rows based on a start and end time. The measure works except when the start and end time crosses midnight. I need help altering the measure to look at the date as well to get an accurate count. file is attached.

Thanks!

[mehmetcik]

Your attachment makes no sense

Try

A15 =TIMEVALUE(TEXT(A11,"hh:mm:ss"))
B15 =TIMEVALUE(TEXT(B11,"hh:mm:ss"))
C15 =IF(INT(B11)>INT(A11),1-A15+B15,0)

[dblackwood72]

Not sure if I explained my problem well enough. I'm not looking for a calculation between the start and exit time. I'm looking for a count in my hour pivot table. So for the last data entry for 9/1/19 12:22 to 9/2/19 2:05, I want a 1 in the 23:00, 0:00, 1:00, and 2:00 rows.

My measure works fine if the start date/time < end date/time, but anything that crosses midnight does not count.

Hope that makes sense.

[JeteMc]

Not sure how/if it can be done with a DAX measure, however here is a formula based proposal.
Populate a column of discrete hours 0:00 ... 23:00
The adjacent column is populated using:

=SUM(SUMPRODUCT((MOD(Table1[Arrival],1)>=I2)*(MOD(Table1[Arrival],1)<SUM(I2,1/24))),SUMPRODUCT((MOD(Table1[Exit],1)>=I2)*(MOD(Table1[Exit],1)<SUM(I2,1/24))),SUMPRODUCT((MOD(Table1[Arrival],1)<I2)*(MOD(Table1[Exit],1)>=SUM(I2,1/24))),SUMPRODUCT((INT(Table1[Arrival])<INT(Table1[Exit]))*(MOD(Table1[Exit],1)>=I2)))-SUMPRODUCT((INT(Table1[Arrival])<INT(Table1[Exit]))*(MOD(Table1[Exit],1)>=I2)*(MOD(Table1[Exit],1)>=I2)*(MOD(Table1[Exit],1)<SUM(I2,1/24)))

Let us know if you have any questions.