Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

Current 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)....
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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-f1c839ee884e

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    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!