Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Zman
Frequent Visitor

relative date filter "in this date"

Hi

In Power BI service, on my report I am displaying today's sales by using relative date filter "in this day".

Today I noticed that until around 10am it was (blank) and after 10am the figures popped up! I am in Australia, so the time difference to UTC is 10 hours. It looks like the relative filter "in this day" uses UTC time - only at 10am it becomes "this day".  

All the date/times displayed in refresh history is Australian time, no problems,so I thought "in this day" would also consider my local time. But not?

How can I overcome the problem? Shall I create a column with -10 hours from the invoice date/time and use that in relative date filter? Does not make sense but I couldn't find anyting on setting up "in this day" etc function/option.

I hope people far away from UTC had the same problem and found a solution.

Cheers

Zman

 

Additional new info: this morning I got a data alert for sales TODAY exceeding the threshold - actually business not started yet, it's 8.30am!! THIS MEANS: Power BI refresh date/time and alert date/times ARE correct, as per the local time. BUT relative date filter date/time is wrong. 

2 REPLIES 2
v-caliao-msft
Employee
Employee

@Zman,

 

TODAY filter in Power BI services is used UTC timezone. You need to add a column to show local time and filter your data, and only the records that date equal to this new added column will be show on your report. Please refer to the link below to see the details.

http://community.powerbi.com/t5/Service/changing-timezone-of-powerbi-service/td-p/153663

 

Regards,

Charlie Liao

@v-caliao-msft

I replied just few minutes ago, but now I cannot see it, so wrinting again:)

 

When I add local time now to my invoices query, all rows have the same value, and nothing is filtered. maybe you meant to add local time to another query and then filter it?

I just wanted to use the "relative date filter" available in visualisations tab. It looks great, simple to set, but not working especially when you have 11 hours time difference to UTC!

I was really in a rush as my client had to see today's invoices this morning (but it was still yesterday in UTC). So, I came up with a quick solution: added local time and the a new column: if Date.From([invoice date]=Date.From([local time]) the "Today" else "Not" and used that as a filter for the visualisation. Worked for today but I want to see what will happen tomorrow.

Of course this is work around - that "Relative date filter" option also has in this week, in this month etc.. that's why I liked it because I could create carsd for today, this week, this month, this year quickly in few seconds. I think for this great option to work power bi needs to make it possible to set your tiem zone that would apply to that filter. Interestingly all my scheduled refreshes are running as set up in my local time.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors