Forum Discussion
Power BI - timezone
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.
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:
- 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
- Use and Enter Data query to create a table called R09_Table with the following data:
Time Timezone 1:00:00 PM EST - Ensure that the Data type for the Time column is set to Time
- 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:
- Create a column in the table R09_Timezones with the following formula:
DAX UTC Offset = [UTC Offset] / 24- 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
)- 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
3 Replies
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:
- 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
- Use and Enter Data query to create a table called R09_Table with the following data:
Time Timezone 1:00:00 PM EST - Ensure that the Data type for the Time column is set to Time
- 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:
- Create a column in the table R09_Timezones with the following formula:
DAX UTC Offset = [UTC Offset] / 24- 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
)- 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
Apologies Anonymous , it is out there now! 🙂