how to find time band using dax in power bi?

Question

I would like to find time band of half hour.Suppose i have a time stamp : 2019-03-12 01:20:00 this exists between time band of 1:00-1:30, similarly 2019-03-12 04:33:00 this exists between time band of 4:30-5:00. I have column of n number of timestamp.

Is there any dax function that i can write to find the time band or there is any other process? Thanks in advance!!

So, I'm assuming you have a table and in that table there is a column of time stamps. Were you hoping to add a new column to your table with a text datatype that contains your 'time band'? Or maybe two new columns that have time fields with 'time band start' in one and 'time band end' in the other? — Ryan B.
I am hoping to add a new column to my table with a text datatype that contains your 'time band' that can find the above mentioned conclusion. — Aditya Ranjan

Ryan B. Ryan B. · Accepted Answer · 2020-02-29T19:22:06

Consider something like this:

TimeBand =
    FORMAT(FLOOR(MyTable[Timestamp], 1/48),"h:mm") 
    & " - " 
    & FORMAT(CEILING(MyTable[Timestamp], 1/48),"h:mm")

Floor and Ceiling are very similar functions -- they essentially round to the nearest multiple. Floor picks the biggest multiple that is smaller, and ceiling picks the smallest multiple that is bigger. Usually we round to powers of 10, but with this function we can round to the nearest 5, or 7, or 1/3.

Combine this with the understanding that PowerBI values all datetimes as a special kind of number -- it is simply the count of days since 12/30/1899. (Try it, create a calculated column and set its value to 0, then display it as a date time). So in this system "1" is one day. That means 1/24 is one hour, and 1/24/60 is one minute. It follows, then, that 30/24/60 is 30 minutes, and that reduces to 1/48.

So now we can take Floor/Ceiling, give it a time, and have it round that datetime to the nearest half-hour -- either down or up depending on which function you choose.

Use format to convert everything to text, and you're all set.

Here's some sample data generated in Power Query:

let
    Source = List.Generate(()=> #datetime(2020,3,1,0,0,0), each _ < #datetime(2020,3,1,0,0,0) + #duration(1,0,0,0) , each _ + #duration(0,0,5,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Timestamp"}, null, ExtraValues.Error)
in
    #"Converted to Table"

And the results of our DAX:

Hope it Helps!

how to find time band using dax in power bi?

1 Answers