Forum Discussion

soundguy2's avatar
soundguy2
Helper I
6 years ago

Relative Date Filtering - date moving ahead too early

I'm attempting to use Relative Date Filters to display sales in the last 1 day (the current calendar date).  It works fine for most of the day but at about 5pm PST the filter moves to the next calend...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi soundguy2 ，

 

1,Go to query editor>Click on the button of date column on the left top corner and choose "Date/Time",see below:

And then you will see what time is set in your date column,here you see:

Then find the time difference between the column and your local time,then add a custom column using the M code below to adjust the time:

 

custom=[Date]+#duration(0,17,51,0)

 

And you will see:

 

 

Save and use the new column in your visual and apply filters.

 

Here is a sample .pbxi file you can refer to.

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

 

soundguy2's avatar
soundguy2
Helper I
6 years ago

Thank you Kelly for trying to solve this issue for me.  Unfortunately, the Relative Date Filter still moves forward at 5PM PST, and no amount of manipulating the data seems to fix that.  

 

For now, it looks like the only solution is not to refresh the report after 5PM (not ideal, but better than a 1PM cutoff).

 

PowerBI Team - Can the functionality of the Relative Date Filter be improved?  It's usefulness is limited if it's not syncronized to the user's timezone.

 

Regards,

Brian

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi soundguy2 ,

     

    It doesnt make sense,as you have modified the datetime column,there shouldnt be any time difference between ...

    Did you use the new date/time column in the date field?

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
    • soundguy2's avatar
      soundguy2
      Helper I
      6 years ago

      Hi Kelly,

       

      There definitely is a time difference when using a date column that has been modified.  Following your directions, I can adjust so that the daily sales is correct after 5PM, however I then see yesterday's sales before 5PM.  The relative date filter always changes at 5PM so there is always some difference before and after that time.  

       

      The real kicker was this evening (April 30) when all April sales disappeared because that report was using the "is in this month" filter.  Unless there's secret way to realign the relative date filter to the user's time, we'll have wait for the Power BI team at Microsoft to address this one.  Thanks again.

       

      Regards,

      Brian

      • nsmither's avatar
        nsmither
        Frequent Visitor
        3 years ago

        I have this issue as well, were you ever able to come up with a solution to this?