Forum Discussion
AdministratorCurrent day employee location
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!
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
4 Replies
- amitchandak
Super User
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
Syndicate_AdminThank 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])returnCALCULATE(Max([Location]), filter(('Presence control') , 'Presence control'[EntryDate] =_max))I think there's something I haven't done right.
Thanks a lot!
MahyarTFMemorable Member
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
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
