Can you set a rolling time period for a report

[Anonymous]

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.

[v-yetao1-msft]

Hi @Anonymous 

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 :

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.

[Anonymous]

Hi @v-yetao1-msft 

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

[amitchandak]

@Anonymous , 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))

[Anonymous]

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).