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

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. 

Hi Folks,

Can you please share your thoughts around this issue?

Thanks in advance.

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