How to compare timestamp with a static time

Power BI
1

Hi, Guys, could you please help me with this challenge? I have a column with timestamp when an order is submitted. I need to compare if this order is submitted before 9:00PM, can I do this in Power Query or PowerBI? and how I can insert the static time (in this case, 9:00PM)?

Thanks!

2

@Mike Thanks for posting your question on the forum. Your question can be best answered if you can share your pbix and source file. You can do this both in power query and power bi but without looking at your data it is not possible to clearly provide the solution

Thanks.

3

Hi, @MudassirAli, here is the attachment. There is one table in the file, which include OrderID and order submitted timestamp. I’d like to compare timestamp to see which order was created 2 hours before 9:00PM.

Thanks for your help!

Mike

store_time_query.pbix (56.8 KB)

4

Hello @Mike,

Is this the type of result you’re expecting? Below are the screenshots provided for the reference -

Inserting Static Time
Inserting Static Time1904×824 60.2 KB

Time Difference Result
Time Difference Result808×665 22.9 KB

I’m also attaching the PBIX file of the working for the reference. I’ve added the static time directly in the Power Query you can go through the simple transformative steps in it.

Just give a shout in case you need anything.

Hoping you find this useful and meets your requirements that you’re looking for. :slightly_smiling_face:

Quick Note: I’ve separate the Submitted Date column into Date and Time columns so that I can use the DATEDIF function in the formula.

Thanks and Warm Regards,
Harsh

store_time_query - Harsh.pbix (45.2 KB)

1 Like
5

@Harsh, Hi, harsh, that is exactly what I was looking for. I will check the file you attached.

Thank you very very much!

Mike

6

Hello @Mike,

You’re Welcome. :slightly_smiling_face:

I’m glad that it met your rquirements.

Surely, please take your time to review the file.

Happy Learning!!!

Thanks and Warm Regards,
Harsh

7

@Mike
You can also do it in Power Query by adding an Hour Column and then creating custom column

1
11920×1080 170 KB
2
21920×1080 158 KB

1
11920×1080 206 KB

Attaching the PBIX and source file for your reference
Table.csv (22.5 KB)
store_time_query.pbix (64.8 KB)

1 Like
8

@MudassirAli That is awesome! Thank you very much!

Mike

1 Like
9

Hi, @MudassirAli @Harsh Now I have more challenge with this one, we need to add store open time for each row. I can add 9:00AM as store open time for each order in Power Query. Now my question is how I can compare the order submission time with store close and open time. I need to flag those orders if the submitted time falls between one hour before store close and store open time (in this case, every orders that were submitted between 8:00PM a to 9:00AM), Can we do that in PowerBI?

Thanks!

10

Hello @Mike,

If possible for you can I request you one thing? Can you please create a new thread for this question since original question that were asked by you had already been answered.

Just mark us both in that thread and I’ll be there with my solution.

Thanks and Warm Regards,
Harsh