Last Sync Date for Smart Watch for each employee

[Viraj1995]

Hi all,

I've been stuck on this measure for some time and was hoping someone here could help.

I have the following data structure (with some dummy data as an example):

Employee number | Timestamp (Clock in Date) |Device Sync Status | Last Synceed(EXPETED RESULT)

1                            | 6th Feb 2021                      | 1                            | 6th Feb 2021

1                            |7th Feb 2021                       | 0                            | 6th Feb 2021

2                            |6th Feb 2021                       | 0                            | 7th Feb 2021

2                            |7th Feb 2021                       | 1                            |7th Feb 2021

The commands I tried involved using Calculate() Filter(), ALL(), IF Statements and more.  I tried both calculated columns and measures but I know I am missing important logic.

Last Date Synced = Calculate(MAX(Timestamp), Device Sync Status = 1)

I will eventually need an ALL() statement integrated into this so it shows the latest date regardless of filtering by date.

Please can someone guide me to completing this measure.

Kind Regards,

[Viraj1995]

Thank you so much this works perfectly!

Some devices have never synced. How could I handle this exception with an if statement. 

(Whenever I go to the version of the table which is filtered to sync = 0, it shows no employees , a blank table basically)

Since it's a date column I am gueissing I need to return a date so maybe the MIN(timestamp) or a default date of jan 1st for now would work great.

I tried If(vResult = Blank(), min(date), vresult) but it doesn't seem to do the trick.

Thanks so much for your help

[DataInsights]

Hi @Viraj1995,

Glad to hear the solution works. Regarding devices that have never synced, would you be able to provide an example (both the data table and how you want the visual to display the data)?

[Viraj1995]

Hi @DataInsights ,

Thanks so much for your help and sorry for the late reply.

So everything on the last sync date is working well. However, the purpose of this calc was to help me achieve the number of days since the device has synced. This should only include the days the empoyees has came to work.

I used this calculation:

Calculate(DISTINCTCOUNT(Timestamp),Filter(vEmployeeTable,[Timestamp] >= [Last Synced] && [Timestamp] <= MAX(Timestamp))

One time it showed me the number days looking like it worked fine but now the table crashes and it says PowerBI has ran out of memory. Can you think of an altenative way to create this meassure that's less intensive in DAX?

Summary of what I want to achieve (assuming today is 8th feb and the employe didn't clock in on this day)

Employee number | Timestamp (Clock in Date) |Device Sync Status | Last Synceed| Days since Synced |

1                            | 6th Feb 2021                      | 1                            | 6th Feb 2021| 2 

1                            |7th Feb 2021                       | 0                            | 6th Feb 2021| 2

2                            |6th Feb 2021                       | 0                            | 7th Feb 2021| 1

2                            |7th Feb 2021                       | 1                            |7th Feb 2021| 1

I can post this on a seperate post if required.

Thank you,

[DataInsights · Accepted solution]

@Viraj1995,

Try this measure:

Last Synced = 
VAR vCurrentEmployee =
    MAX ( DeviceSync[Employee Number] )
VAR vEmployeeTable =
    FILTER (
        ALLSELECTED ( DeviceSync ),
        DeviceSync[Employee Number] = vCurrentEmployee
    )
VAR vResult =
    CALCULATE (
        MAX ( DeviceSync[Timestamp] ),
        vEmployeeTable,
        DeviceSync[Device Sync Status] = 1
    )
RETURN
    vResult