Forum Discussion

AvinashPentyala's avatar
AvinashPentyala
Frequent Visitor
1 year ago
Solved

Relative Time with Slicer

Hi All,

 

I'm experiencing an issue with my slicer not displaying the correct time when I select the last hour on desktop. The column data includes timestamps up to the current time.

Could you please suggest a solution to ensure that it shows the time interval from 8 to 9 when I select the last 1-hour range?

 

Thank you!

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi AvinashPentyala ,
    Thanks for Kaviraj11 reply.
    Here is another possible solution


    As stated in the documentation, the anchoring time is based on UTC time, so you can create a column to display the UTC time and then filter based on the UTC time and use it  in slicer. Here are two ways to create it.
    Create a calculate column in dax(My local time zone is UTC+8)

    UTC Time = 'Table'[TIMESTAMP] - 8/24

    Or create in power query and you need to change the type as DTAE/TIME/TIMEZONE

    = Table.AddColumn(#"Changed Type", "Custom", each DateTimeZone.ToUtc(DateTime.AddZone([TIME],8)))


    The timeframe itself cannot be changed, but you can create a Mesur as a cade to override the original Slicer period

    Duration = CONCATENATE(FORMAT([Last ONE HOUR],"HH:MM:SS AM/PM") & " - ", FORMAT(NOW(),"HH:MM:SS AM/PM"))

    Final output

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

     

     

6 Replies

  • Kaviraj11's avatar
    Kaviraj11
    Solution Sage
    1 year ago

    Hi,

     

    Adjust Time Zone Settings:

    • If your data includes timestamps in UTC, you might need to adjust for your local time zone.
    • You can create a new column in Power BI to adjust the time. For example: 
      LocalTime = Table[Timestamp] + TIME(YourOffsetHours, 0, 0)

       

    • AvinashPentyala's avatar
      AvinashPentyala
      Frequent Visitor
      1 year ago

      The data is in timestamp format but not in UTC. Additionally, I’m encountering the following error when adding a column.

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi AvinashPentyala ,
    Thanks for Kaviraj11 reply.
    Here is another possible solution


    As stated in the documentation, the anchoring time is based on UTC time, so you can create a column to display the UTC time and then filter based on the UTC time and use it  in slicer. Here are two ways to create it.
    Create a calculate column in dax(My local time zone is UTC+8)

    UTC Time = 'Table'[TIMESTAMP] - 8/24

    Or create in power query and you need to change the type as DTAE/TIME/TIMEZONE

    = Table.AddColumn(#"Changed Type", "Custom", each DateTimeZone.ToUtc(DateTime.AddZone([TIME],8)))


    The timeframe itself cannot be changed, but you can create a Mesur as a cade to override the original Slicer period

    Duration = CONCATENATE(FORMAT([Last ONE HOUR],"HH:MM:SS AM/PM") & " - ", FORMAT(NOW(),"HH:MM:SS AM/PM"))

    Final output

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

     

     

    • AvinashPentyala's avatar
      AvinashPentyala
      Frequent Visitor
      1 year ago

      Hi Anonymous 

      Thank you for providing the PBIX file. I tried both options, but they aren't working. I've attached my PBIX file—could you please check if I'm missing anything?

       

      PBIX File