How to create a Time Table to analyze your Power BI or Excel data

If you want to know how to analyze your data by hour, minute, half hour etc in Power BI or Excel Power Pivot then I will show you how to build your own Time Table and how to round your data to the nearest minute 🔽 More Info below 🔽
00:00 Intro
00:29 Explaining the data and goal
01:35 Splitting Date Time
02:10 Round to nearest minute using Modulo
04:08 Round to nearest minute using Column From Examples
05:48 Create a Time Table
10:48 Connect Fact table to Time Table and produce charts
Find a copy of the Time Table on our website
accessanalytic.com.au/free-ex...
Video on Calendar Table
• What is a Date Table /...
Follow me on LinkedIn
/ wynhopkins
Twitter
/ wynhopkins
Access Analytic Training
accessanalytic.com.au/training

[@IvanCortinas_ES]

Direct, effective, powerful, productive. As always, perfect. Thank you for the explanation Wyn!

[@AccessAnalytic]

Thanks Ivan, greatly appreciated

[@yeeau]

Only just came across this video...absolutely brilliant! and very well explained. Thanks

[@AccessAnalytic]

You’re very welcome

[@guymorales]

I just came across this great video easy to follow and implement. Thanks

[@AccessAnalytic]

You’re welcome Guy. I appreciate you taking the time to let me know you found it useful

[@darktjo6578]

Usually I don't leave comments, but man, THANK YOU!!!!! The solution is simple and your explanation!!! Simple and direct!!! Outstanding!!!! Thanks again!!

[@AccessAnalytic]

You’re welcome. Thanks for taking the time to leave a kind comment

[@ewuolablessingomolola9769]

Thank you so much, this video helped me achieve exactly what I was trying to

[@AccessAnalytic]

Awesome, thanks for letting me know it helped you

[@dlerouxa]

Very clear explications. Your english is easy to understand moreover. Thanks a lot

[@AccessAnalytic]

You’re welcome

[@gabrielximenes6895]

Thanks! Very good explanation with lots of good bits of power bi to learn! Excelent video!

[@AccessAnalytic]

Thanks Gabriel!

[@nataliiaiatsenko7779]

Use again your video. You are the best teacher!

[@AccessAnalytic]

Cheers 😀

[@kelechie.2011]

Awesome. Straight to the point and helpful

[@AccessAnalytic]

Thanks

[@darlingtonezemadu5647]

The explanation is concise and comprehensible. After couple of searches, this just saved my life. Thank you very much.

[@AccessAnalytic]

Excellent! I appreciate you taking the time to let me know you found it useful

[@d20207]

OMG!!!! This is the only video that explains time intervals in such an easy way. There other videos which explain the same objective but with DAX formulas and are so complicated!! this methodology is so easy to understand. Thank you!!

[@AccessAnalytic]

You're welcome! Thanks for taking the time to leave a kind comment

[@mabenba]

Thanks for this very informative tutorial! It really helped me a lot.

[@AccessAnalytic]

Great to hear Matias, thanks for taking the time to leave a kind comment

[@natah1284]

Just shared this channel with coworkers… worth every second!

[@AccessAnalytic]

Greatly appreciated Nata H! Thanks

[@AnkitGupta-cn1zd]

You have the solution to all the query ...Thanks a ton

[@AccessAnalytic]

You’re welcome Ankit

[@user-xn2sj3jt5s]

Amazing video.

[@AccessAnalytic]

Thank you!

[@lorenzoladejobi8701]

Very educative! Thank you

[@AccessAnalytic]

You’re welcome Lorenzo

[@SamiElzaim]

Great one ! Thank you.

[@AccessAnalytic]

Cheers Sami

[@vinipowerbi]

Thank you so much , beautifully explained

[@AccessAnalytic]

You’re welcome.

[@anthonypasslow1933]

Great work and presentation, thank you

[@AccessAnalytic]

You're welcome Anthony

[@SaniGarba]

This is a great solution. Thanks.

[@AccessAnalytic]

Thank you Sani

[@mnowako]

Excellent! Thank you!

[@AccessAnalytic]

You’re welcome Mariusz

[@Fyllype]

You are on another level! thanks

[@AccessAnalytic]

Hah! Thanks ☺️

[@bentatlim]

I love love love your videos :) They are so helpfull !!!! Amazing ! THANK YOU !!!!

[@AccessAnalytic]

Too kind! Thank you 😀

[@ChrisSmithFW]

This is great. Thank you very much.

[@AccessAnalytic]

You’re welcome Chris

[@nataliiaiatsenko7779]

Thank you very much. You helped a lot!

[@AccessAnalytic]

No worries 😁. Thanks for letting me know

[@wayneedmondson1065]

Hi Wyn. Excellent! Some very good tips and techniques there. Thanks for sharing :)) Thumbs up!!

[@AccessAnalytic]

Cheers Wayne, you never know when some bits will come in handy

[@loricksho515]

Thank you so much! This solution helped a lot!

[@AccessAnalytic]

No worries. I appreciate you taking the time to let me know you found it useful

[@oconnorra]

Thanks a lot man! Good stuff

[@AccessAnalytic]

Cheers Ryan

[@zcc7156]

super awesome and easy to follow

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@me58123]

very helpful!!

[@AccessAnalytic]

Thanks for letting me know

[@alaahassan135]

Realy amazing, Thanks

[@AccessAnalytic]

Thank you Alaa

[@matthewgalport6096]

Fantastic tutorial

[@AccessAnalytic]

Glad it helped 😀

[@FRANKWHITE1996]

Thanks for sharing! 👍

[@AccessAnalytic]

Thanks for commenting 😀

[@Bhavik_Khatri]

Very nice tutorial 👍

[@AccessAnalytic]

Thanks Bhavik 😃, I'm glad you're finding these videos useful

[@sonyse2t5]

Ace presentation.

[@AccessAnalytic]

Thank you

[@leofrittrang9095]

Great Video, Thanks a lot! Is it somehow possible to show the last value for all empty 5 minute time slots? I have a data source that only gives out a new value output once there is a change to the value, if there is no change over a longer period of time that means that the value stays same.

[@AccessAnalytic]

Thanks Leo. That’s a more complex scenario than I can answer here without understanding the data more. I’d suggest posting some screenshots and details of what you are trying to do here community.powerbi.com/t5/Desktop/bd-p/power-bi-designer

[@androtele9887]

Thank you for this video. It is helpful. I’m learning a lot of new things. I don't know if can help.I have challenge that currently I was not able to sort out. I need to plot big dataset where I have as X axle the time (gg/mm hh:mm) and as Y axle several numerical series. Usually, I’m able to plot them with Excel, but when I need to go granular, Excel frozen (in the best case). I need that, because first I have a look to the whole trend as general and then zoom in where there are some discontinuities. To zoom in, I’m filtering the table. Is there an easy and smarter way to do this with Excel or Power Bi or? Thank you for any suggestion.

[@AccessAnalytic]

Power BI has a zoom slider feature for axis, plus there are a few custom visuals like this that may help blog.pragmaticworks.com/power-bi-custom-visuals-time-brush

[@androtele9887]

@@AccessAnalytic Thank you very much

[@momo70467]

@access Analytics. this an amazing video. I learned a lot. I am however looking for a way basically to define business day / date. in Bar/Nightclub company the regulation requires reporting business revenue based on the day the business day started: Monday business starts 6am and end 5:59:59 am on Tuesday and so on for the rest of the week. I need help figuring out a way to create a table visual that reflects the numbers that way. right now the way I do it cuts all day revs at midnight ... please help!

[@AccessAnalytic]

Hi Mo, maybe the query in this link is what you need? aasolutions.sharepoint.com/:x:/s/PubliclyAvailableContent/EbFw84-KNGlGkKk0IhcVNyoBRycR3JuR5N1UTFGxp4Q9Ww?e=Cxw0Iq After opening Go to Save As and download a copy

[@bendunford8732]

Win, this is terrific. Any advice on how to sort the hour time slots in chronological time order (12:00am in the morning through the day to 11:59pm?) I can't seem to make it work on my end.😀

[@AccessAnalytic]

You might need to add an index column and then use the sort by button

[@AccessAnalytic]

Although unless the column is text it should sort in numerical order.

[@macrokiler]

Wyn! Nice job over there! I wonder if I have "start_time" and "end_time" columns, visualization-wise, how could I approach this? Should I use only the started_time column? Only the ended_time? Cause here I wouldn't have a "master time column". If anyone can help me.

[@AccessAnalytic]

Thanks Bruno, If you need to report on both then bring in both and connect both to your Calendar. One will be the active primary solid line relationship and the other will be an inactive dotted line. You then use the USERELATIONSHIP function in DAX to trigger the dotted line as required.

[@macrokiler]

@@AccessAnalytic Ok! Thank you so much! Enjoying your channel a lot.

[@bgkearns]

Great video, How would I create a measure to calculate a running total across both the time and calendar tables?

[@AccessAnalytic]

Maybe something like // Sales Lifetime to Date (LTD) Calculation Sales LTD = VAR MaxDate = MAX('Calendar'[Date]) VAR MaxTime = MAX('Time'[Time]) VAR LifetimeSales = CALCULATE ( [Sales], FILTER ( ALL('Calendar', 'Time'), 'Calendar'[Date]

[@AnkitGupta-cn1zd]

It seems quite easy, I have just a small issue, How to proceed when I am actually working on my data set, to perform each and every task consumes 15-20 min. makes the easy things look bit messy. If you can suggest on this that would be a great help to me.

[@AccessAnalytic]

Can you explain a bit more

[@TookieBunten]

Would love something like this for minutes, seconds and hundredths. MM:SS.00 and converting numbers stored as text to that format. Can never get power query to do it

[@AccessAnalytic]

I don't know the answer to that sorry

[@souzaeq]

Nice video; really helped. I still have one doubt: I need to divide the value to split among the days, wich time dax or transformation in power query should I use like to divide the total revenue by the number of days and then create a line for each recurrence. Like: USD 100/ 4 days, is USD 25/ day, how can I automatically generate starting from current information as 4/jan/2023 (end date) with 100 USD to 1/jan/2023 with USD 25, 2/jan/2023 with USD 25, 3/jan/2023 with USD 25 and 4/jan/2023 with USD 25 ?

[@AccessAnalytic]

Bit too tricky to explain here sorry. I'd suggest posting to one of the forums such as community.fabric.microsoft.com/t5/Desktop/bd-p/power-bi-designer Or techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral or www.reddit.com/r/PowerBI/new/

[@sravankumar4860]

Hi Sir, Can you please help me how to find the overlapping start and stop dates for patients using Excel. Thanks.

[@AccessAnalytic]

Hi, would need more information and examples sorry. You should post the question with some example data and more description here techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589

[@juanarturosilvaordaz8827]

Hello, does anyone know the measure for "Number of Units"? It'd be great if you could share it

[@AccessAnalytic]

There isn’t one measure that fits. It’s dependent on the structure and names of tables and columns. This isn’t specifically Time related is it? Is it not =SUM( TableName( ColumnName) )

[@Avais880]

HI, when i use this method time stamp 5:00:00 convert to Time to min 4:59:00, how can i avoid this?

[@AccessAnalytic]

I'm not sure sorry , i'd suggest posting a screenshot and example file here techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral

[@pabeader1941]

I love you. I want to have your children. All kidding aside. I have been looking for ANYBODY talking about actual TIME info. Now if we can just get the Power Platform folks to add TIME to their ecosystem. And change the name to Period Intelligence instead of Time Intelligence (which it isn't)

[@AccessAnalytic]

Hah thanks 😂

[@ashokganne9116]

How can we create time bucket 9 to 10 hr, 10 to11 hr based on time table

[@AccessAnalytic]

I’d try using Column from Examples and type in a few different examples on the relevant rows. Otherwise you’ll need to write a custom column formula

[@tonyrooibos]

This looks like too much hard work, compared to other products, (Matlab, R, KNIME, etc) . IMO Power BI is not a great tool for time series analysis, as it does not handle time easily. A time stamp to even milli-seconds should be able to be handled transparently. Maybe MS will add such functionality going forward.

[@AccessAnalytic]

It would be great if there were built in buttons / ui features to help people build their own Date and Time tables

[@tonyrooibos]

@@AccessAnalytic - There are other tools I have available, some since the early nineties, where the tool automatically handles timestamps from years down to fractions of a second, with no need for wrangling of timestamps eg to separate and handle data and times separately. Even EXCEL can sort of do this. Why cannot Power BI do this too ? Or maybe it can ? For exmaple I may have timestamps as yyyy-mm-dd HH:MM:SS and possibly 10, 000 columns, 100 of millions of rows, and want to plot say an XY with X = time, Y - any variable quickly. No fuss. ???

[@AccessAnalytic]

Power BI won’t handle 10000 columns ( normally you unpivot those columns to make longer thinner tables, but with 100 million rows it’s unlikely to cope with that, you’d need some earlier database view prep I’d imagine. You can display date time on a graph

[@tonyrooibos]

@@AccessAnalytic Thanks for the advice. The data is not amenable to aggregating etc, but that is another story.

[@IncredibleAdventure1]

Simpler Way NearestMinuteTime = ROUND([TIME_STAMP]*1440,0)/1440

[@IncredibleAdventure1]

I found out that if you do it this way the relationships in the model do not work. You have to do it in Power Query Editor

[@AccessAnalytic]

Always advisable to add new columns in Power Query rather than DAX