Power BI - timezone

[Anonymous]

Hi,

I am trying to think of a function or conditional column that I am able to use for the following situation: 

Employee works 2/16 at 11:00pm (CST) and clocks in at 11:01:29 PM equals late because he didn't clock in at the schedule time. 

But,  because employee works from 11pm-7am, the system reads that employee is schedule at 12am on 2/17 and therefore is late because the clock in shows 12:03:29 AM. It should be counting the 11:01:29 PM clock in as late and not the 12:03:10 AM. 

See chart below: 

UserID	Date	Schedule	Login	Late
jrex38	Monday, February 16,2019	2/16/2020 11:00:00 PM	2/16/2020 11:01:29 PM	No
jrex38	Monday, February 17,2019	2/17/2020 12:00:00 AM	2/17/2020 12:03:10 AM	Yes

Does anyone have any idea what I could to fix this?

Thank you in advance. 

[Greg_Deckler · Accepted solution]

In my next book coming out this month, I have a recipe for time zone conversion. Here is a brief excerpt from DAX Cookbook by Packt coming out this month. See if it helps.

To prepare for this recipe, do the following:

1. Open Power BI Desktop and create a table called R09_Timezones by importing the file Timezones.csv file from GitHub here: https://github.com/gdeckler/DAXCookbook/Ch03
2. Use and Enter Data query to create a table called R09_Table with the following data:

Time	Timezone
1:00:00 PM	EST

4. Ensure that the Data type for the Time column is set to Time
5. Create a relationship between the Timezone column in the table R09_Table and the Abbr. column in the table R09_Timezones

How to do it...

To implement this recipe, do the following:

1. Create a column in the table R09_Timezones with the following formula:

DAX UTC Offset = [UTC Offset] / 24

2. Create a measure using the following formula:

TZ Convert = 
    VAR __DestTZ = "ACWST"
    VAR __SourceTime = MAX('R09_Table'[Time])
    VAR __SourceTZ = MAX('R09_Table'[Timezone])
    VAR __SourceOffset = 
        LOOKUPVALUE(
            'R09_Timezones'[DAX UTC Offset],
            'R09_Timezones'[Abbr.], 
            __SourceTZ
        ) 
    VAR __DestOffset = 
        LOOKUPVALUE(
            'R09_Timezones'[DAX UTC Offset],
            'R09_Timezones'[Abbr.],
            __DestTZ
        ) 
    VAR __UTCTime = __SourceTime + -1 * __SourceOffset
RETURN
    IF(
        ISBLANK(__SourceTime),
        BLANK(),
        __UTCTime + __DestOffset
    )

3. Create a Table visualization and place the Time and Timezone columns from the table R09_Table as well as the measure TZ Convert into the Values field for this visualization

[Anonymous]

Hi @Greg_Deckler ,

I was not able to find the csv file on Ch03. Is it still out there?

Thank you!

[Greg_Deckler]

Apologies @Anonymous , it is out there now! 🙂