Current day employee location

[Syndicate_Admin]

Good morning!

HR departments ask me to create as a table where you can see from where employees sign (if from HOME, OFFICE or OTHERS) on the current day.

We sign twice a day (entry and exit). For example, the following would have to come out:

USER	DAY	TYPE	LOCATION
Andrea	26/07/2022	Entrance	HOUSE
Andrea	26/07/2022	Exit	HOUSE
Martin	26/07/2022	Entrance	OFFICE
Martin	26/07/2022	Exit	OFFICE
Edgar	26/07/2022	Entrance	OFFICE
Edgar	26/07/2022	Exit	OFFICE

Currently he puts me every day, but I only need the current day or, failing that, the last one signed:

I have the data, but I do not know how to make it appear only on the current day or, failing that, the last day signed. Can you help me?

Thanks in advance and greetings!

[Anonymous · Accepted solution]

Hi @Syndicate_Admin ,

Please refer this formula.

Column = 
var last_day = CALCULATE(MAX('Table'[DAY]),FILTER('Table','Table'[USER]=EARLIER('Table'[USER])&&'Table'[TYPE]=EARLIER('Table'[TYPE])))
return
CALCULATE(MAX('Table'[LOCATION]),FILTER(ALLEXCEPT('Table','Table'[USER],'Table'[TYPE]),'Table'[DAY]=last_day))

Best Regards,

Jay

[MahyarTF]

Hi,

Not sure what you mean, Would you please put your expectation as a sample in your post ?

But if you want to see just the first day, (day = 1), you could put the filter on your visual/page or report

[amitchandak]

@Syndicate_Admin , You need to have timestamp for that or add index column in power query

Last Location = var _max = maxx(filter(ALLSELECTED(Data), Data[USER] = Max(Data[USER]) && Data[Date] = Max(Data[Date])), Data[Time])
return
CALCULATE(max(Data[Location]), filter((Data) , Data[Time] =_max))

Refer

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[Syndicate_Admin]

Thank you very much, but it does not work, I have put the formula that you have told me in the column and in ALL users I get that they work from the office when it is not so. For example, this user last signed yesterday from CASA:

I have used the formula that you have passed me, but adapted to my data:

Last Status = where _max = maxx(filter(ALLSELECTED('Presence control'), 'Presence control'[User] = Max('Presence control'[User])), 'Presence control'[EntryDate])

return

CALCULATE(Max([Location]), filter(('Presence control') , 'Presence control'[EntryDate] =_max))

I think there's something I haven't done right.

Thanks a lot!