Date modification by filtered timezone

[dilkushpatel]

scenario:

I have a filter using which user can select country

Now once country is selected I want to modify all dates to timezone of that country

I have created measures which works fine as they work within context of selection, however this measures will not help as I want to show charts where i show rowcount within hour band which is based on date in appropriate timezone.

Calculated columns nor working as it considers whole timezone table while calculating and will not consider selected timezone

Any idea how can i achieve this?

[Anonymous]

So when you say you want to modify dates to that timezone of the country do you mean that you want to have the Date/Time shift up or down based on where they are on the GMT?

My solution would be to firstly work out what Timezone your dates are recorded as.  For this example i'll presume that they represent a date/time in Greenwich (so GMT = 0).

Now create a Timezone table with 2 column, the Name of the timezone for your dropdown and the GMT modifier.  Here in Brisbane Australia, our modifier is GMT + 10.  Time data is stored as a decimal number between 0 and 1, so for Brisbane i'd need to store (10 / 24) as my modifier.  If you have a place that was GMT - 10, you'd need to store (-10 / 24).  This column we'll call [Timezone] and set as a number.

Now whenever you wish to display a date/time, your measure just needs to show [DateField] + MIN([Timezone]). Since your user will have selected a timezone, this will always return that selected value.  (If non is selected, you'll get the Lowest GMT value).

[dilkushpatel]

@Anonymous

I agree with you till the point of measure will work as long as we show each date on report

how ever when aggregates are required it will fail

Consider scenario,

I have calculated TimeBand depending on during which hour call was started

Now I have to show number of calls by this hour Band then it will need band as dimension and count of either dates or id column as measure

Now with this report if I use Modified call dt which is measure then t will not work as each date is not selected.