Days since the device last synced measure (or column)

[Anonymous]

Hi all,

I hope you're well.

I have the following data:

EmployeeID | Clock in date | Sync? | Last Sync Date (Measure)| 

1                  | 23rd Feb         |True     | 23rd Feb                           

1                  | 19th Feb         |True     | 23rd Feb                           

1                  | 17th Feb         | False   | 23rd Feb                           

2                  | 21st Feb         | False    | 18th Feb                          

2                  | 18th Feb        | True     | 18th Feb                 

3                  |19th Feb          | True   | 19th Feb   

3                 |20th Feb          | False   | 19th Feb                          

3                  |23rd Feb         | False    |19th Feb     

I want to achieve the following:

- The number of days the employee has clocked in after the last sync date. We don't include days the employee did not visit the office. If an employee clocks in once between 1st feb  (Synced) and 1st march (not synced) then the employee has not synced for 1 day as opposed to a month. 

EmployeeID | Clock in date | Sync? | Last Sync Date (Measure)|  Days Since Sync (EXPETED RESULT)

1                  | 23rd Feb         |True    | 23rd Feb                           |1             (i.e 23rd  is both the last sync and clock in  date = 1 days)

1                  | 19th Feb         |True    | 23rd Feb                           |1

1                  | 17th Feb         | False  | 23rd  Feb                          |1

2                  | 21st Feb         | False   | 18th Feb                           |2          (i.e 18th last sync and last clock is 21th.t = 2 days)

2                  | 18th Feb         |True    | 18th Feb                           |2

3                  |19th Feb          | True   | 19th Feb                          |3          (i.e 19th last sync and last clock is 23rd therefore 3 days.)

3                  |20th Feb          | False   | 19th Feb                          |3

3                  |23rd Feb         | False    |19th Feb                          | 3

This is the measure I tried and it does return correct numbers but also creates duplicates in my data. It's important to remember the last synced column is a measure too.:

Days Since Sync =
VAR vCurrentEmployee = MAX('Table[EmployeeNo])
VAR vEmployeeTable = FILTER(ALL('Table'),'Table'[EmployeeNo] = vCurrentEmployee && 'Table'[Clock in Date] >= [Last Synced])

Return

CALCULATE(DISTINCTCOUNT(Table[Clock in Date ]),vEmployeeTable)

Please can someone try help me with this.

-----------------------------------------

Extra information if required:

As you can see, employee 2 last synced on the 18th and his last clock in date was the 21st of Feb. Therefore it has been 2 days of clock ins since he last synced. 

(Essentially the unique dates clocked in for each employee where the clockin date > last sync date)

For employee 3, we see that he clocked in on the 19th of Feb, 20th and the 23rd of Feb. He last synced on the 19th and visited the office 2 more times without syncing therefore he has not synced in 3 days (3 unique dates between last sync and future clock ins)

[amitchandak]

@Anonymous , Create three measures like and try

Last Sync Date = maxx(filter(allselected(Table), Table[employee id] = max(Table[employee id]) && table[Sync?] = true()),Table[Clock in date])

Last Clock in date = maxx(filter(allselected(Table), Table[employee id] = max(Table[employee id]) && table[Sync?] = true()),Table[Clock in date])

Days since sync = datediff([Last Clock in date], today(), day)