Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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 there @itsmebvk
This is a thorny problem with Power BI.
A couple of points to bear in mind:
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:
Something to note about UTC & UTC+10:
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
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
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.
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.
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.
Once created, you can apply a filter on the column of the Calculation Group table for the specific Calculation Item.
Regards,
Owenn
Hi Folks,
Can you please share your thoughts around this issue?
Thanks in advance.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |