Display data by selected date type

[cutestjigs]

Hi there,

I want to display data in different time zone based on which time zone is selected by the user. If user select China time zone, then data should display in china time zone. 

For example, Our system records order invoice date in UTC Time zone. So, I have added 2 columns in table, "Invoice date china" and "Invoice date NZ". But i don't have any idea about how to display time zone name in radio button and how to show data based on selected time zone.

table name:  "invoice"

columns: id, invoice_date_utc, invoice_date_china, invoice_date_nz, sales_amount, order_id

I want to calculate no. of orders, total sales in particular time period.

Thanks,

Jigu Patel

[Seward12533 · Accepted solution]

One way is to forget your extra columns. You need to use a disconnected slicer (table not linked to other tables in your model with the list of time zones and offsets from UTC. Then harvest the selected value using SELECTEDVALUE

OFFSET = SELECTEDVALUE(timezones[Offset],0)

DisplayDateTime= average(table[invoicedatetimeutc)+TIME(offset)

Or if you want the 3 columns your time zone table just needs to have the time zone names.

TIMEZONE=SELECTEDVALUE(timezones[zone],”UTC”) // selects or default to utc

Display Time = SWITCH(TRUE(),
“China”, average(table[invoivedatetimechina],
“NZ”, average(table[incoicedatetimenz],
average(table[incoicedatetimeutc])

And then use Display Time in your visual.

[zlokesh]

Hi @cutestjigs,

         Please go through below details.  I got the same on comminuty only.

Please change the values according to the time zone. I hope will work.

We can switch time zone when add a custom column. I have tested it on my local environment.

[Seward12533 · Accepted solution]

One way is to forget your extra columns. You need to use a disconnected slicer (table not linked to other tables in your model with the list of time zones and offsets from UTC. Then harvest the selected value using SELECTEDVALUE

OFFSET = SELECTEDVALUE(timezones[Offset],0)

DisplayDateTime= average(table[invoicedatetimeutc)+TIME(offset)

Or if you want the 3 columns your time zone table just needs to have the time zone names.

TIMEZONE=SELECTEDVALUE(timezones[zone],”UTC”) // selects or default to utc

Display Time = SWITCH(TRUE(),
“China”, average(table[invoivedatetimechina],
“NZ”, average(table[incoicedatetimenz],
average(table[incoicedatetimeutc])

And then use Display Time in your visual.

[cutestjigs]

Hi @Seward12533,

I tried to get timezone using

TIMEZONE=SELECTEDVALUE(timezones[zone],”UTC”) // selects or default to utc

but only shows default UTC, it do not change on selection.

Thanks,

[Seward12533 · Accepted solution]

Did you create a separate table with only a list of time zones in it or are you trying to slice from a column in your table? Please check that you are using a slider visual not a table. Can you share a workbook that demonstrates this behavior? All you need is your slicer table and selected timezoe measure and a card visual to display selected time zone.

[erikajain02]

Hi,

For showing Time Zone Name ...You can select a Slicer.

Then go to Format Option > Selection COntrols > Single Select ON.

For dispalying the value : Choose a card from the  Visualisations  , then SELECTEDVALUE('Table'[ColumName])