Convert date field to a selected date time zone to calculate adjusted values

[22koconnor]

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!

[Anonymous]

HI @22koconnor,

I think this should be related to multiple level aggregations in measure calculations. You can refer to the Greg’s blog to add variable with summarize function to apply the first level aggregate and use iterator function to summary previous calculations results.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[22koconnor]

I appreciate your assistance! I will take a look at this and do some testing. I will update the thread once I have finished my research. Thanks again.

[Anonymous]

Hi @22koconnor ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng