Converting and showing times zones from MST,GMT and selected timezone based on the country selection

[vkr057]

Hi Team,
I have a requirement of showing times zones( MST,GMT local timezone) based on the country selected in the Country filter.
Could we able to achieve by using Dax. we don't have previlige to create an custom column. 

Reference Image:

[rohit1991 · Accepted solution]

HI @vkr057 ,

To dynamically display MST, GMT, and local times based on country selection in Power BI using DAX:

Create DAX Measures:

UTC : CurrentUTC = NOW() - TIMEZONEOFFSET(),

MST : MST_Time = CurrentUTC + (LOOKUPVALUE('Offsets'[MST Offset], 'Offsets'[Country], SELECTEDVALUE('CountryTable'[Country])) / 24),

GMT: GMT_Time = CurrentUTC,

Local Time: Local_Time = CurrentUTC + (LOOKUPVALUE('Offsets'[Local Offset], 'Offsets'[Country], SELECTEDVALUE('CountryTable'[Country])) / 24)

Add these measures to a table or card visual. Use the Country filter to dynamically update times. Ensure the Offsets table maps countries to their respective time zone offsets.

[AnkitKukreja · Accepted solution]

Hi! @vkr057 

Prepare a table with the mapping of countries to their respective time zones. For example:

Country	TimeZoneOffset	TimeZoneAbbreviation
USA (MST)	-7	MST
UK (GMT)	0	GMT
India (IST)	5.5	IST

Add a Country Slicer to your report.

Measure for LocalDateTime =
VAR SelectedOffset = SELECTEDVALUE(TimeZoneTable[TimeZoneOffset])
RETURN
IF(
NOT ISBLANK(SelectedOffset),
DATEADD(Dataset[UTCDateTime], SelectedOffset * 60, MINUTE),
BLANK()
)

Measure  for MSTDateTime = DATEADD(Dataset[UTCDateTime], -7 * 60, MINUTE)

Measure for GMTDateTime = DATEADD(Dataset[UTCDateTime], 0 * 60, MINUTE)

[rohit1991 · Accepted solution]

HI @vkr057 ,

To dynamically display MST, GMT, and local times based on country selection in Power BI using DAX:

Create DAX Measures:

UTC : CurrentUTC = NOW() - TIMEZONEOFFSET(),

MST : MST_Time = CurrentUTC + (LOOKUPVALUE('Offsets'[MST Offset], 'Offsets'[Country], SELECTEDVALUE('CountryTable'[Country])) / 24),

GMT: GMT_Time = CurrentUTC,

Local Time: Local_Time = CurrentUTC + (LOOKUPVALUE('Offsets'[Local Offset], 'Offsets'[Country], SELECTEDVALUE('CountryTable'[Country])) / 24)

Add these measures to a table or card visual. Use the Country filter to dynamically update times. Ensure the Offsets table maps countries to their respective time zone offsets.