Most recent 8hour slicer with hourly update

[mangeski]

Im looking to capture hourly data that is in the last 8 hours and the page updated hourly. 

For example:

     If it's midnight my slicer will show a timeframe of 4pm-12am.  When the current time changes 1am, my slicer will show a timeframe of 5pm-1am.  There's not alot to go off of here so I appoligize in advance.   Thanks team

[Anonymous]

Hi @mangeski,

Thanks for reaching out to the Microsoft fabric community forum.

Since Power BI Service operates in UTC, you'll first need to convert UTCNOW() to your local time zone. As @danextian

and @Bmejia both responded to your query, kindly go through their responsees and check if the issue will be resolved. 

I would also take a moment to thank @danextian and @Bmejia, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[danextian · Accepted solution]

Hi @mangeski 

Assuming refresh will be in the service, you'll need to make sure that NOW() is converted to your local time zone. Power BI Service uses UTC.

_time =
VAR _TimeZoneOffset = 8
VAR _UTCNow =
    UTCNOW ()
VAR _NowLocalTimeZone =
    _UTCNow + DIVIDE ( _TimeZoneOffset, 24 )
VAR _NowCurrentHour =
    DATE ( YEAR ( _NowLocalTimeZone ), MONTH ( _NowLocalTimeZone ), DAY ( _NowLocalTimeZone ) )
        + TIME ( HOUR ( _NowLocalTimeZone ), 0, 0 )
VAR _8HoursBefore =
    _NowCurrentHour - DIVIDE ( 8, 24 )
RETURN
    IF (
        table[datetime] >= _8HoursBefore
            && table[datetime] <= _NowCurrentHour,
        "Current Timeframe",
        "Other"
    )

If you want the timeframe itself to be returned try this:

_time =
VAR _TimeZoneOffset = 8
VAR _UTCNow = UTCNOW()
VAR _NowLocalTimeZone = _UTCNow + DIVIDE(_TimeZoneOffset, 24)
VAR _NowCurrentHour =
    DATE(
        YEAR(_NowLocalTimeZone),
        MONTH(_NowLocalTimeZone),
        DAY(_NowLocalTimeZone)
    ) +
    TIME(
        HOUR(_NowLocalTimeZone),
        0,
        0
    )
VAR _8HoursBefore = _NowCurrentHour - DIVIDE(8, 24)

VAR _StartHour = HOUR(_8HoursBefore)
VAR _EndHour = HOUR(_NowCurrentHour)

VAR _StartLabel = 
    FORMAT(TIME(_StartHour, 0, 0), "h AM/PM")
VAR _EndLabel = 
    FORMAT(TIME(_EndHour, 0, 0), "h AM/PM")

VAR _Label = _StartLabel & " - " & _EndLabel

RETURN
    IF(
        Location[sunrise (iso8601)] >= _8HoursBefore &&
        Location[sunrise (iso8601)] <= _NowCurrentHour,
        _Label,
        "Other"
    )

Power BI by default doesnt update the selection so if the selection changes, your visuals might return blanks. You will need to leverage the Group by Columns property to "store a filter by using an alternate value, which represents the key of the entity"  in Tabular Editor. Power BI uses this key to store the filter, allowing the corresponding filter value in the visual to change dynamically. Please see this demo. Further details are in the video description. https://youtu.be/MrEAZREQuXM 

[mangeski]

Hey Dane,

I tried putting in the first formula in the DAX queries but I'm getting this error :  

Query (2, 5) The syntax for '_TimeZoneOffset' is incorrect. (_time =
VAR _TimeZoneOffset = 8
VAR _UTCNow = UTCNOW()
VAR _NowLocalTimeZone = _UTCNow + DIVIDE(_TimeZoneOffset, 24)
VAR _NowCurrentHour =
DATE(
YEAR(_NowLocalTimeZone),
MONTH(_NowLocalTimeZone),
DAY(_NowLocalTimeZone)
) +
TIME(
HOUR(_NowLocalTimeZone),
0,
0
)
VAR _8HoursBefore = _NowCurrentHour - DIVIDE(8, 24)

VAR _StartHour = HOUR(_8HoursBefore)
VAR _EndHour = HOUR(_NowCurrentHour)

VAR _StartLabel =
FORMAT(TIME(_StartHour, 0, 0), "h AM/PM")
VAR _EndLabel =
FORMAT(TIME(_EndHour, 0, 0), "h AM/PM")

VAR _Label = _StartLabel & " - " & _EndLabel

RETURN
IF(
Location[sunrise (iso8601)] >= _8HoursBefore &&
Location[sunrise (iso8601)] <= _NowCurrentHour,
_Label,
"Other"
)
).

Not sure what I'm doing wrong. I feel like I'm missing something.  Here is a pic of my page just for visual purposes.  

[Bmejia]

Hi,
Power BI will only allow you to schedule a refresh up to 8 times, so will not be able to do it during a 24 hour period or  24 times a day.   

Once you determine the refresh times.  You can add a calculated column to your table to filter your data, so you can pull 8 hours only from the time you refresh the report.  (Filter on 1)

Date 8HR Filter =

If(

    Your_Table[ date]>=NOW()-TIME(8,0,0)

        && Your_Table[ date]<=NOW()

        ,1,

        0)

[mangeski]

I'm not sure of what my refresh times are going to be currently. Just by understanding your response, I might need a different approach. The data is being entered hourly at all hours. If something happened or changed within the last 1-4 hours, seeing that report rather than just pulling it in excel with power query would be helpful. Maybe this could be done better in excel?