cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
uthall Regular Visitor
Regular Visitor

Group record last 24hrs by hour

Hi,

I have a recent post that groups record per hour for today.

I now want to group them by hour for the last 24hrs.

Can anyone do a variation on this to achive this for mer?

https://community.powerbi.com/t5/Desktop/Grouping-records-per-hour/m-p/721731#M348326

Thanks

Jeff

10 REPLIES 10
Super User
Super User

Re: Group record last 24hrs by hour

So what's different between now and the previous question?  If you only want data from the previous 24 hours, you can add a restriction to your filter that only searches data from within the previous 24 hours.

 

Also, you can possibly re-use your previous measure, applying a different context:

PreviousDayValue = CALCULATE([Previous Measure], FILTER('Table1', 'Table1'[Time] > NOW()-1))
uthall Regular Visitor
Regular Visitor

Re: Group record last 24hrs by hour

Hi there,

Whats different is that the current method uses a 0 to 24 table that doesnt recognise that 1500 to 1700 on day 1 is a different day to 1400 to 1600 on day 2....

Does you suggestion below help with this?

Super User
Super User

Re: Group record last 24hrs by hour

Is there another column in your data that specifies the date for each entry?

 

To ask another way, if you handed me your table of start and stop times, how would I as a person figure out which row applies for which day?

uthall Regular Visitor
Regular Visitor

Re: Group record last 24hrs by hour

Absolutly, there is a datetime column with around a weeks worth of realtime data.

Super User
Super User

Re: Group record last 24hrs by hour

Then filter based on that datetime field.

If you only want to use data from the past 24 hours, use the expression:

PreviousDayValue = CALCULATE([MeasureThatWorks], FILTER('Table1', 'Table1'[DateTime] > NOW()-1))

You're FILTERing Table1 and only calculating [MeasureThatWorks] on rows where the DateTime is after (NOW - 1 day), which is 24 hours ago.  Feel free to edit the filtering expression to include/exclude data as you see fit. 

 

If you wanted data from the day before yesterday, you could change the condition to

[DateTime] < NOW()-1 && [DateTime] > NOW()-2

Which returns data where the datetime is earlier than 24 hours ago, and after 48 hours ago. You can change it to only use data that fits whatever condition you describe.

uthall Regular Visitor
Regular Visitor

Re: Group record last 24hrs by hour

Ok, thanks,

However the records are being grouped by hour only, so how does the grouping know that 2300 on day 1 is a difference group set to 0100 on day 2?

Super User
Super User

Re: Group record last 24hrs by hour

I don't remember the original measure we ended up using, but it's because that measure grouping them by the value of HOUR(start time).  It can distinguish between 23 and 1 easily.

However, it would NOT distinguish between 2300 on day 1 and 2300 on day 2.  We got around this by limiting the query to now - 24 hours, so the same time can't appear twice.  If you wanted to be more specific, you could explicitly group by both hour and date, so that there's no chance of data from different days mixing.

Highlighted
uthall Regular Visitor
Regular Visitor

Re: Group record last 24hrs by hour

no drama,

the original query is in the link at the top of the post.

Any help on modifying the query would be awesome......

Super User
Super User

Re: Group record last 24hrs by hour

I'm down to help you update it, but first what problem behavior are you seeing with the current setup?  Is it not grouping properly? Is it not filtering properly? Something else?

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 26 members 933 guests
Please welcome our newest community members: