Filter out incomplete hours

[cassidy]

Hello,

I need a way to report on only sales orders that are from a complete hour.  

Example:

I refresh Power BI at 1 p.m. and it brings over sales records with a Max order time of 12:15 p.m.  Since 12-1 p.m. is only partially reporting, I don't want to report it at all.  I need to filter the records to be <12:00 p.m., the last complete hour.  It might not always be the Current Time - 1 hour, so looking for a solution that doesn't rely on the current time.

Each row as a Date/Time stamp down to the second.

[parry2k]

@cassidy add a column as a flag and then filter on it

Filter Orders= 
VAR __datetime = TODAY() + TIME ( HOUR ( NOW() ),0,0)
RETURN
IF ( Table[Order DateTime] < __dateTime, 1, 0 )

now you can set filter where Filter Order column is equal to 1

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[cassidy]

Hi, thanks for the response. 

I believe this is saying "If the Order DateTime is < the Current Hour, let it through".  Unfornuatley the delay between our Sales database and PowerBI is too unpredictable for that.

Here is an example using that Calculated field:

As of 2:30 PM, the MAX OrderDateTime I have is 01:08:59 PM.  The hour of 1 PM to 2 PM is incomplete, so I do not want it to be counted.  I think with that formula, because the OrderDateTime was in the < Current Hour, it shows up as "1".

[parry2k]

@cassidy can you add a column with the following expression and put it in the table visual, want to check what value you get, maybe we need to tweak this.

Filter Time = 
TODAY() + TIME ( HOUR ( NOW() ),0,0)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[cassidy]

It shows as 4 PM, but as you can see by new MAX OrderDateTime is 2:12:04 PM, so I only want to select orders with a OrderDateTime between 01:00:00 PM and 01:59:59 PM

[parry2k · Accepted solution]

@cassidy let's try again,  add the following measure and then add it in the visual and see what you get

if you don't see the result, create separate measure and return __maxStartDate and __maxStartEnd and put in table visual to see if we get the correct start and end range

Measure = 
VAR __maxDateTime = CALUCLATE ( MAX ( Table[OrderDate] ), ALL ( Table ) )
VAR __maxDate = DATE ( YEAR ( __maxDateTime), MONTH ( __maxDateTime), DAY ( __maxDateTime) )
VAR __maxDateEnd = __maxDate + TIME ( HOUR ( __maxDateTime ), 0 , 0 )
VAR __maxDateStart = __maxDate + TIME ( HOUR ( __maxDateTime ) - 1, 0, 0 )
RETURN
CACLULATE ( COUNTROWS ( Table ), Table[OrderDate] >= __maxDateStart, Table[OrderDate] < __maxDateEnd )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[cassidy]

This works, thank you!

I'm going to try and replicate it on the query editor side too  (prefer it there).  But I get the concept now.  Thanks!