How to create a Time Table to analyze your Power BI or Excel data
Vložit
- čas přidán 30. 07. 2024
- 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 - Jak na to + styl
Direct, effective, powerful, productive. As always, perfect. Thank you for the explanation Wyn!
Thanks Ivan, greatly appreciated
Only just came across this video...absolutely brilliant! and very well explained. Thanks
You’re very welcome
Usually I don't leave comments, but man, THANK YOU!!!!! The solution is simple and your explanation!!! Simple and direct!!! Outstanding!!!! Thanks again!!
You’re welcome. Thanks for taking the time to leave a kind comment
Awesome. Straight to the point and helpful
Thanks
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!!
You're welcome! Thanks for taking the time to leave a kind comment
The explanation is concise and comprehensible. After couple of searches, this just saved my life. Thank you very much.
Excellent! I appreciate you taking the time to let me know you found it useful
Use again your video. You are the best teacher!
Cheers 😀
Just shared this channel with coworkers… worth every second!
Greatly appreciated Nata H! Thanks
Thank you so much, this video helped me achieve exactly what I was trying to
Awesome, thanks for letting me know it helped you
Very clear explications. Your english is easy to understand moreover. Thanks a lot
You’re welcome
Hi Wyn. Excellent! Some very good tips and techniques there. Thanks for sharing :)) Thumbs up!!
Cheers Wayne, you never know when some bits will come in handy
Thanks for this very informative tutorial! It really helped me a lot.
Great to hear Matias, thanks for taking the time to leave a kind comment
I just came across this great video easy to follow and implement. Thanks
You’re welcome Guy. I appreciate you taking the time to let me know you found it useful
You have the solution to all the query ...Thanks a ton
You’re welcome Ankit
Great one ! Thank you.
Cheers Sami
This is a great solution. Thanks.
Thank you Sani
Very educative! Thank you
You’re welcome Lorenzo
Thanks! Very good explanation with lots of good bits of power bi to learn! Excelent video!
Thanks Gabriel!
Excellent! Thank you!
You’re welcome Mariusz
Thank you so much , beautifully explained
You’re welcome.
Great work and presentation, thank you
You're welcome Anthony
I love love love your videos :) They are so helpfull !!!! Amazing ! THANK YOU !!!!
Too kind! Thank you 😀
Thanks a lot man! Good stuff
Cheers Ryan
You are on another level! thanks
Hah! Thanks ☺️
This is great. Thank you very much.
You’re welcome Chris
Thank you very much. You helped a lot!
No worries 😁. Thanks for letting me know
Thank you so much! This solution helped a lot!
No worries. I appreciate you taking the time to let me know you found it useful
Thanks for sharing! 👍
Thanks for commenting 😀
super awesome and easy to follow
Thanks for taking the time to leave a kind comment
very helpful!!
Thanks for letting me know
Fantastic tutorial
Glad it helped 😀
Realy amazing, Thanks
Thank you Alaa
Amazing video.
Thank you!
Ace presentation.
Thank you
Very nice tutorial 👍
Thanks Bhavik 😃, I'm glad you're finding these videos useful
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.
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
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.
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
@@AccessAnalytic Thank you very much
@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!
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
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.
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.
@@AccessAnalytic Ok! Thank you so much! Enjoying your channel a lot.
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.😀
You might need to add an index column and then use the sort by button
Although unless the column is text it should sort in numerical order.
Great video, How would I create a measure to calculate a running total across both the time and calendar tables?
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]
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.
Can you explain a bit more
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 ?
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/
Hi Sir, Can you please help me how to find the overlapping start and stop dates for patients using Excel. Thanks.
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
Hello, does anyone know the measure for "Number of Units"? It'd be great if you could share it
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) )
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
I don't know the answer to that sorry
HI, when i use this method time stamp 5:00:00 convert to Time to min 4:59:00, how can i avoid this?
I'm not sure sorry , i'd suggest posting a screenshot and example file here techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral
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)
Hah thanks 😂
How can we create time bucket 9 to 10 hr, 10 to11 hr based on time table
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
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.
It would be great if there were built in buttons / ui features to help people build their own Date and Time tables
@@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. ???
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
@@AccessAnalytic Thanks for the advice. The data is not amenable to aggregating etc, but that is another story.
Simpler Way
NearestMinuteTime = ROUND([TIME_STAMP]*1440,0)/1440
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
Always advisable to add new columns in Power Query rather than DAX