Convert to 30 minutes Interval

fb1990
fb1990 used Ask the Experts™
on
Hello EE,

Can someone please help me with how to convert date and times to 30 minutes interval in Microsoft Power BI.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It could be:

=TIME(HOUR(NOW()),INT(MINUTE(NOW())*2)/2,0)

Open in new window

Top Expert 2014

Commented:
I don't know how you would convert dates into 30 minute intervals.  However, there are 2880 such intervals in each day.  So, that conversion might be:
=(Now()-Date())\2880

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
aikimark,
You may want to edit your Comment.

With 24 hours in a day, there are only 48 of those 30 minute long intervals. But if you meant to calculate the number of 30 second intervals, there are 2880 of those.

Brad
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

AndyAinscowFreelance programmer / Consultant

Commented:
I could argue there are probably an infinite number of thirty minute intervals in a day.
One starts now, the next start 0.00000000001 seconds later.....

You need to specify rather more information as to just exactly what you require and how a thirty minute interval is started.
Top Expert 2014

Commented:
too late now. :-(

I started writing the formula and had 30 second intervals in mind.

Author

Commented:
@byundt and aikimark

That is exactly what I wanted.  Like in this example
0:00:00
0:30:00
1:00:00
1:30:00
2:00:00
2:30:00
3:00:00
3:30:00
4:00:00
4:30:00
5:00:00
5:30:00
6:00:00
6:30:00
7:00:00
7:30:00
8:00:00
8:30:00
9:00:00
9:30:00
10:00:00
10:30:00
11:00:00
11:30:00
12:00:00
12:30:00
13:00:00
13:30:00
14:00:00
14:30:00
15:00:00
15:30:00
16:00:00
16:30:00
17:00:00
17:30:00
18:00:00
18:30:00
19:00:00
19:30:00
20:00:00
20:30:00
21:00:00
21:30:00
22:00:00
22:30:00
23:00:00
23:30:00

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you multiply the time value by 48, the integer part of the result is a bracket number from 0 to 47. I would add this as a new column in your data rather than a PowerBI measure.
=INT(48*(DateAndTime - INT(DateAndTime)))

Or if you want the interval to appear as a time, then just divide the previous formula by 48:
=INT(48*(DateAndTime - INT(DateAndTime)))/48
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That will return a number.

See my original post. Of course, NOW() can be replaced with any date/time (cell) value.

Author

Commented:
I will try Gustav's original post in the morning.  

@byundtMy data has several million row rows in .csv that I am importing to Power BI
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The formula I posted works in PowerBI Desktop, where I applied it to the data after importing it. Many of the functions have the same names in Power BI's M and DAX languages as they do in Excel.

Author

Commented:
This formula in excel worked as I expected

TEXT(FLOOR(DATEANDTIME,TIME(0,30,0)),"HH:MM")

Thanks all for your help
Commented:
TEXT(FLOOR(A2,TIME(0,30,0)),"HH:MM")

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial