Convert date field to a selected date time zone to calculate adjusted values
Hello, My overall goal is to have users select their desired time zone and see the number of calls answered, based on their time zone selected. My thought process was to convert the call_date time based on the time zone selected from a separate table called 'Time Zones.' This time zone table accounts for Daylight savings and contains all time zone offsets in minutes, depending on where each call_date is between the begin_date and end_date. Here is an example from the Time Zone table: I did this by creating a measure: VAR CurrentDate = MAX('Call Segment'[call_date]) VAR TheZone = IF(ISBLANK(SELECTEDVALUE('Time Zones'[Time Zone Name])), "Greenwich Standard Time", SELECTEDVALUE('Time Zones'[Time Zone Name])) VAR TimeConversion = MAXX( FILTER( 'Time Zones', 'Time Zones'[Time Zone Name] = TheZone && 'Time Zones'[begin_date] <= CurrentDate && 'Time Zones'[end_date] >= CurrentDate ), 'Time Zones'[UTC Offset Minutes] ) RETURN IF( TimeConversion < 0, CurrentDate - TIME(0, ABS(TimeConversion), 0), CurrentDate + TIME(0, TimeConversion, 0) ) The part I am struggling with is counting the number of Call IDs that fall within the newly converted time. I need to show Call Counts (Sum of Call IDs) by this new date, exaclty like below but will dynamically adjust based on that Time Zone: Any help would be greatly appreciated!
