Date Column Date (UTC) to Localtime (AEST) Issue

[itsmebvk]

Hi Folks,

I am facing some date issue related to timezone.

I have a Date table (Azure analysis Service Model is source) in that table i have a Date column and other date related fields.  When I use Last 7 days filter in filters pane, even though I am on 28th its still showing days based on UTC time now. Instead of showing 21st to 27th its showing dates between 20th to 26th. (I understood based On Microsoft document Relative date filter works based UTC time)

To fix this issue I tried to convert my Date column in power query to "Date/Time/Timezone", but when i try to filter my date using this new column it is still showing dates based on UTC. I tried most of the solutions suggested in the community.

I also tried to create calculated column using following expression but still same issue.

DateTimeZone.SwitchZone([Date],10)

I also checked AAS model, but there is no way i can convert it to "Date/Time/Timezone"

Am i missing something here?  Is it because I i don't have time part in my Date or is there any setting in PBI Desktop overwriting this value?

Please suggest?

Thanks in advance.

[olgad]

Hi, 
what about adding 7 day filter in Power Query?
add column TodaysDate 
= Date.From(DateTimeZone.FixedUtcNow())
Change the data type to Date
then 

if [Date] <= [TodaysDate] then if [Date] >= Date.AddDays([TodaysDate] , -6) then 1 else 0 else 0

If today needs to be excluded then 
if [Date] <[TodaysDate] then if [Date] >= Date.AddDays([TodaysDate] , -7) then 1 else 0 else 0
And use it as a filter to filter for last 7 days. 

[OwenAuger]

Hi there @itsmebvk 

This is a thorny problem with Power BI.

A couple of points to bear in mind:

• As you've observed, in the Power BI Service, relative date slicers use current UTC date/time as the reference.
• If timezone information is added to a datetime type column in Power Query (producing a datetimezone type) the column loaded to the Power BI dataset does not retain timezone in any way. Timezone information is basically discarded. A Power BI or Analysis Services dataset supports only datetime values without timezone information.

How to solve this?

Just to be clear on the requirements (hopefully I have this right 🙂 ), you want to take the current date in local time (UTC+10), then apply a filter corresponding to the 7 days preceding this.

So on 28-Jan in the UTC+10 timezone, the filter should be:

• Dates in the range 21-Jan to 27-Jan
• Or in terms of datetime, datetime values that are >= 21-Jan 0:00 and < 28-Jan 0:00.

Something to note about UTC & UTC+10:

• In UTC+10, from 10:00 to just before 0:00, UTC+10 date = UTC date.
• In UTC+10, from 0:00 to just before 10:00, UTC+10 date = UTC date + 1.

This means we can't rely on an additional column to help with filtering, because the inclusion of a given date varies during the day.

Instead, I would suggest creating a calculation group, with a calculation item that applies the filtering by converting UTC to local timezone.

The calculation item would have to

• Determine LocalDate = UTCNOW() + 10 hours (just the date part).
• Apply a filter on Calendar_Date, from LocalDate - 7 to LocalDate -1

The DAX expression for the calculation item would be:

VAR UTC_Offset_Hours = 10
VAR LocalDate =
    CONVERT (
        INT ( UTCNOW () + UTC_Offset_Hours / 24 ),
        DATETIME
    )
VAR MinDate =
    LocalDate - 7
VAR MaxDate =
    LocalDate - 1
VAR Result =
    CALCULATE (
        SELECTEDMEASURE (),
        DATESBETWEEN ( 'Dim_Date'[Calendar_Date], MinDate, MaxDate )
    )
RETURN
    Result

Then apply this calculation item as a filter on the page or visuals as required. Note that this only applies to the calculation of measures, so I am assuming that this is sufficient for the visuals you are creating.

If you're new to calculation groups, check out SQLBI's series of articles.

Hopefully this is useful. Please post back if needed.

Regards

[itsmebvk]

Thanks @olgad @OwenAuger fro your inputs.

With your direction I am able to achieve my requirement in slightly different way. I created an import table using following expression and filtered using that column. That solved my issue .

define var utctime = now()
       var correcteddate = datevalue(utctime+(11/24)) 
       var _Previous7thday =  correcteddate -7
       var _Yesterday=  correcteddate -1

evaluate

summarize(
          calculatetable(Dim_Date
			,Dim_Date[Date]>=_Last7thday
                        ,Dim_Date[Date]<=_Yesterday)
			,Dim_Date[Date])
						
						 
order by Dim_Date[Date] desc

Really appreciate for your direction.

[itsmebvk]

H @OwenAuger ,

Thanks alot for taking time and sharing your inputs.

Sorry I didnt understand how we need to filter on visual or page using the measure you suggested. New is the measure which i created, Can you please suggest what we need to filter here?

Thanks in advance.

[OwenAuger]

Hi @itsmebvk 

No problem 🙂
Just to clarify, I was suggesting creating a Calculation Group and that code was for a Calculation Item within the Calculation Group.

See here for the general method to create these.

https://learn.microsoft.com/en-us/training/modules/dax-power-bi-calculation-groups/calculation-group...

Once created, you can apply a filter on the column of the Calculation Group table for the specific Calculation Item.

Regards,

Owenn

[itsmebvk]

Hi Folks,

Can you please share your thoughts around this issue?

Thanks in advance.