Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MalOSB
Frequent Visitor

Getting a report to run from 8am

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

1 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

View solution in original post

7 REPLIES 7
v-yuta-msft
Community Support
Community Support

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

Thank you very much. I will try it now

 

Malcolm

MalOSB
Frequent Visitor

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

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

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.