Getting a report to run from 8am

[MalOSB]

Hi

I want the report I am creating to always show the data since 8am, we are a 24hour business so at 2am, I need it to show the data we have since 8am the day before. This means the relative date filtering does not work for me.

I have been using Crystal previously and this would have been easy to setup, any ideas on the best way to get PowerBI to do this?

Thanks in advance, 

Malcolm

[v-yuta-msft]

Hi MalOSB,

As a general solution, you can create a measure like this pattern to achieve your requirement:

Result =
VAR Eight_Am_Of_LastDay =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 1 )
        + TIME ( 8, 0, 0 )
RETURN
    CALCULATE (
        aggregation,
        FILTER ( Table, Table[DateTime] <= NOW () && Table[DateTime] >= 8am_Of_LastDay )
    )

Hope it's helpful to you.

Jimmy Tao

[MalOSB]

Thank you very much. I will try it now

Malcolm

[MalOSB]

PowerBI says that "aggregation" is not a valid Table, variable or function name?

[v-yuta-msft]

Hi MalOSB,

It's just a pattern, aggregation should be replaced by the aggregation function(e.g.: SUM(), MAX(), MIN(), COUNT()) you want to use to achieve the aggregated value.

Regards,

Jimmy Tao

[MalOSB]

Thanks Jimmy

Since I want the whole report to run from this date/time I wanted to put it in the page filter? or would I have to make each visual use the DAX expression?

Thanks again, 

Malcolm

[v-yuta-msft]

Hi MalOSB, 

Page level filter can only implement simple filter operation like "great than", "less than", "is not" and so on. So in your senario I'm afraid you should measure instead of page level filter.

Regards,

Jimmy Tao

[MalOSB · Accepted solution]

Hi

All seems to be working great except that when I refresh the report in the PowerBI service the report is missing all the current hour's data. In the Desktop app it shows the data correctly and even the pinned table from the report in a dashboard shows the current hours information but the report itself in the power BI service will not show the current hours data it just shows all information up to the latest hour.

I hope that makes sense. I thought it might be a UTC issue but I cannot see anything that would stop the data, especially as the pinned table is updating correctly on the dashboard. 

Many Thanks

Malcolm

Update - I seem to have fixed it by placing +(1/24) after NOW ()

+ (1/24) being my timezone