cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DamonKerr
Helper II
Helper II

Can you set a rolling time period for a report

Hi again,

 

Just trying to get a report that shows the last 24 hours of activity as at a certain time.

 

Problem is I'm in Australia (UTC +10) so asking for a Relative Time of "in the last 24 hours" gives me data from 4:00am yesterday to 4:00am today. I've checked this in both Desktop and Service and it gives the same result. Perhaps I should just move to Greenwich?

 

What we need is data from 2:00pm yesterday to 2:00pm today.

 

I am also concerned that regardless of dataset refreshes, users will get a different answer should they run the report at a different time (e.g. if the dataset refreshes at 2:00pm, someone running the report at 2:05 might see 1695 actions, but runnning it at 2:15 might see 1627 actions if some of those actions were undertaken yesterday between 2:05 and 2:15) if I use the "last 24 hours" filter. It seems that when I refresh the report the relative time range changes (but to 10 hours ago).

 

It's not going to be fun working for 6 more months on this if every day I have to change the filter on the report to be manually "from 19/08/2021 2:00pm to 20/08/2021 2:00pm" and republish it at the exact right time so that the data is accurate.

 

Can you specify in some sort of parameter or formula that the date range is <yesterday> 2:00pm to <today> 2:00pm and know that the report will work?

 

The only other option I see is to give data from the last 1 day so it is out of date by the time the report is released at 2pm the next day.

 

Thanks again.

4 REPLIES 4
Ailsa-msft
Community Support
Community Support

Hi @DamonKerr 

You can use DATESINPERIOD dax to return the value in the last 24 hours .

Measure = CALCULATE(SUM('Table'[Value]),DATESINPERIOD('Table'[Date],TODAY(),-1,DAY))

The result is as shown :

Ailsamsft_0-1629785356391.png

I have attached my pbix file ,you can refer to it .

 

Best Regards

Community Support Team _ Ailsa Tao

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

Hi @Ailsa-msft 

 

I tried this without success, needing 24 hours as at a point in time, not tied to a specific date.

amitchandak
Super User
Super User

@DamonKerr , based on what I got

 

Measure  =

var _now = now()

var _now1 = now() -1

return

calculate(sum(Table[Value]), filter(Table, Table[Datetime] >=_Now1  &&  Table[Datetime] <=_now))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thanks for that, I thought this was going to be the solution, however without refreshng the dataset I have lost data from when I refreshed it at 4:30pm yesterday to 9:30am today. What I am wanting is some measure that takes the time of the last data refresh and then calculates the 24 hours previous. So when a user accesses the report, it is as at the time the data was rereshed not time calculated from when they run the report (I suspect we will have major issues if someone looks at the report at 3pm and someone else loooks at it at 4pm and the numbers are different. I'll keep trying. I tried to use the last refresh (I forget where I got the code for this) but the measure will not pull the field in (says it doesn't recognise it).

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.