Calculating first and last entry for a particular date from a table

[raionkar]

Hi there, 

I am building a time attendance sheet by using data from access server. I have a coloumn in a table where I have all the instances when a employee left or entered the building during a date range.

But I want to get first and last enteries on a particular day. Can anyone help me with achieving that ? 

[amitchandak]

Please refer

https://community.powerbi.com/t5/Desktop/Get-First-and-Last-Values-from-Table/td-p/80219

[raionkar]

@amitchandak , thanks for your reply.. I think that's for a record that has mutliple enteries for just one day. In my case, I have a table where I have multiple enteries for a date range..  Please check the picture 

[v-shex-msft]

HI @raionkar ,

You can add a column to the stored date part of EVTime, then write a measure to calculate first/last DateTime based on current date group.  After these steps, you can create a table visual with date as category and measure as detail value to display corresponding records.

EVDate =
DATEVALUE ( Table[EVTime] )

Measure =
VAR current =
    SELECTEDVALUE ( Table[EVTime] )
VAR _min =
    CALCULATE (
        MIN ( Table[EVTime] ),
        FILTER ( ALLSELECTED ( Table ), [EnterArea] = 1 ),
        VALUES ( Table[EVDate] )
    )
VAR _max =
    CALCULATE (
        MAX ( Table[EVTime] ),
        FILTER ( ALLSELECTED ( Table ), [ExitArea] = 1 ),
        VALUES ( Table[EVDate] )
    )
RETURN
    IF ( current IN { _min, _max }, current )

Regards,

Xiaoxin Sheng

[raionkar]

Hello @v-shex-msft ,

I am getting a syntax error when I type in the forumla for var. I am using PowerBI desktop. 

[v-shex-msft]

HI @raionkar ,

Can you please share the detail error message with a snapshot?

BTW, I share two formulas: calculated column and measure, please not use them in the same calculated filed. ('EVDate' is calculated column and 'measure' is measure)

Regards,

Xiaoxin Sheng

[amitchandak]

There is an option. use summarize. There you can take min and max time.

https://docs.microsoft.com/en-us/dax/summarize-function-dax