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
uthall
Helper II
Helper II

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
Cmcmahan
Resident Rockstar
Resident Rockstar

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

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?

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?

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

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.

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?

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.

no drama,

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

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

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?

Great, ok, so lets go back to the basics

I have a table of values

1. Column Startdate (datetime) and Column Endate (datetime). 

2. Column CustomerName

The duration between StartDate and EndDate could be many hours, and cross days.

I want a table/graph showing total unique customer count per hour for the last 24 hrs, inlcuding the hours between StartDate and Endate where there is no unique record. So for a record where StartDate is 27/06/2019 2200 to 28/06/2019 0100 i want a value of 1 for 2200, 2300, 0000, and 0100. Obviously cumulative if more records for other customers span this period.

The previous measure was OK, however where the dates crossed days, it broke down as it only grouped by hour, and did not account for date. It aslo did not filter the last 24hrs.

Hoping this is clear?

 

 

 

 

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.