Count Entry and exit times

Question

I have a table with the fields: Name Date Type Time Index and Times

The index column has this formula: =CONTAR.SI($A$2:A2,A2)
The times column has this formula: =IF(A3=A2,D3-D2,"")

Those 2 columns are additional to my table

What I want is to load my original table in power pivot and from there add the 2 columns and with dax formulas

Take out the index and get the difference between times.

To later be able to verify how long a user was inside and how long he was outside.

I need to know for each day, how long the user was inside and how long outside, for that I need the time difference.

In excel it is easy to achieve since the formula is manipulated, but I have not been able to do it in dax.

Could someone help me please?

anonymous · Accepted Answer

Hi&nbsp;MaritoDominguez&nbsp;,
&nbsp;
You could create two calculated columns as follows.
Index = COUNTROWS(FILTER('Table',[Hora]&lt;=EARLIER('Table'[Hora])&amp;&amp;[Nombre]=EARLIER('Table'[Nombre])))

Diff = var _pre=CALCULATE(MAX('Table'[Hora]),FILTER('Table',[Index]&lt;EARLIER('Table'[Index])&amp;&amp;[Nombre]=EARLIER('Table'[Nombre])))
return IF(ISBLANK(_pre),BLANK(),[Hora]-_pre)

&nbsp;
You can download the attachment for more details.
&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Count Entry and exit times

1 Reply

Related content

Counting nulls

count of previous entries

Count distinct duplicate entries across entity

Subtract Time Entries

Count duplicate entries once