How to change Relative Filtering in PowerBI to Local time

Power BI
1

Hi,
I have a created PowerBI report to filter data using Dimdate for 1 relative day . But PowerBI changes the Filter dates at 8am local time(UTC time) which I want to change it to Local time.
I tried following

  1. Changed data type of Dimdate to Datatime,zone
  2. Changed Zone to Perth time/ +8 UTC.
    Filters still change at 8am
    Now, for short fix
  3. I have created Localtime column in Dimdate and populated with localdate
  4. Created IsToday column by comparing localtime with Date column of dimdate table
  5. I am filtering which ever rows matches IsToday =1 in Report.
    This method is working only for 1 day.

Please suggest is there any simple way to do.

2

Before I go try to re-invent the wheel here have you had a look through this article before?

These has a few options to work with they may help quite a bit with this one.

It does also seem like maybe your solution is a decent one. Is it actually producing the a result you need right now or am I reading that incorrectly?

3

Hi,
I did go thru the Article before. I am not sure how will creating a measure or column in Power Query on localtime changes relative date filtering on Dimdate table. I am little confused here could you help me understand any additional steps I need to do to achieve this.

4

Ok maybe I’m not understanding what you exactly need then. Are you able to add some images of what you currently have? I’m struggling to imagine all the moving parts.

In part 5 you mentioned you actually have this working? Is that right? When you say the method only work for 1 day, I’m not quite sure what this means. Do you want it for 2 days or something like this?

5

PFA is the screenshot that has relative date filtering from Dimdate. The dates in that won’t change until 8 am which is my local time as Its defaults to UTC. I want those dates to be changed on my local time. Currently the solution I have will only work for 1 day. I can send PBIX file its its still not clear

Capture
Capture.JPG405×865 64.8 KB

6

Hi Sam,
Solution I have works only for 1 day. I need solution that works for weekly and Yearly for relative dates.

7

Ok thanks, I wasn’t even close to imagining this was the issue.

The short answer from me is I don’t actually know if you can.

I have done some researching though and I am doubtful at the moment if it is possible. (I’m hoping I’m worng but this is just my view from what I have read)

The detail in this web page provides the most info

Specifically this part

image
image1052×335 36 KB

Does seem like there’s a reasonable limitation to using this at the moment

8

Thanks a lot for Suggestion. I have come up with solution to pass date local date range in case statement to calendar and not using relative dates anymore.

9

Ok great

10

Hi Guptaopus,

Can you please share how you have done this?

Cheers,

Phil