This is by far the most effective way of dealing with time based data set for visualization in PoweBi. I have an initial datetime data sets and have been experimenting with multiple ways I came across here in RUclips, e.g. creating new measures, new table, dax, which I think was complicating my data and me being more confused as a result. What a lifesaver. Thank you! You are an excellent teacher :)
Usually I don't leave comments, but man, THANK YOU!!!!! The solution is simple and your explanation!!! Simple and direct!!! Outstanding!!!! Thanks again!!
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!!
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
@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
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.
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.
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
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.😀
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
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
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, i tried to do this on my data but when I click the date columns it redirects me to a table, while clicking the time columns do the thing that your date columns do. what could be the problem on this it seems like my columns work the other way around
I don’t understand sorry. Could you explain more. What do you mean “when I click the date column”. Is there a specific part of the video you are referring to?
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) )
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)
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 - 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
Direct, effective, powerful, productive. As always, perfect. Thank you for the explanation Wyn!
Thanks Ivan, greatly appreciated
This is by far the most effective way of dealing with time based data set for visualization in PoweBi. I have an initial datetime data sets and have been experimenting with multiple ways I came across here in RUclips, e.g. creating new measures, new table, dax, which I think was complicating my data and me being more confused as a result. What a lifesaver. Thank you! You are an excellent teacher :)
You’re very welcome. Thanks for taking the time to leave your thoughtful comment
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
Very clear explications. Your english is easy to understand moreover. Thanks a lot
You’re welcome
Such a wizard. Really great stuff thank you!
You’re welcome. Thanks for taking the time to leave a kind comment
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
Thank you so much, this video helped me achieve exactly what I was trying to
Awesome, thanks for letting me know it helped you
Use again your video. You are the best teacher!
Cheers 😀
Thanks! Very good explanation with lots of good bits of power bi to learn! Excelent video!
Thanks Gabriel!
Great and neat explanation! Thanks
Glad to help.
Only just came across this video...absolutely brilliant! and very well explained. Thanks
You’re very welcome
Just shared this channel with coworkers… worth every second!
Greatly appreciated Nata H! Thanks
You have the solution to all the query ...Thanks a ton
You’re welcome Ankit
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
Thank you so much , beautifully explained
You’re welcome.
Awesome. Straight to the point and helpful
Thanks
This is a great solution. Thanks.
Thank you Sani
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
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
@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
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]
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 😀
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
Thank you very much. You helped a lot!
No worries 😁. Thanks for letting me know
You are on another level! thanks
Hah! Thanks ☺️
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.
This is great. Thank you very much.
You’re welcome Chris
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
super awesome and easy to follow
Thanks for taking the time to leave a kind comment
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
Fantastic tutorial
Glad it helped 😀
Great one ! Thank you.
Cheers Sami
Thank you.
You’re welcome.
Very nice tutorial 👍
Thanks Bhavik 😃, I'm glad you're finding these videos useful
Very educative! Thank you
You’re welcome Lorenzo
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.
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
Excellent! Thank you!
You’re welcome Mariusz
Thanks a lot man! Good stuff
Cheers Ryan
Amazing video.
Thank you!
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
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/
Realy amazing, Thanks
Thank you Alaa
Ace presentation.
Thank you
very helpful!!
Thanks for letting me know
Thanks for sharing! 👍
Thanks for commenting 😀
Hi, i tried to do this on my data but when I click the date columns it redirects me to a table, while clicking the time columns do the thing that your date columns do. what could be the problem on this
it seems like my columns work the other way around
I don’t understand sorry. Could you explain more. What do you mean “when I click the date column”. Is there a specific part of the video you are referring to?
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) )
Is there a way to get the same outcome but within Excel - not using Power BI?
Within the Excel data model yes. You could also use XLOOKUPS and a standard pivot
@@AccessAnalytic how do you replicate the BI Chart set up that you did when using Excel data model?
@MonteBStJohns you can create a Pivot Chart
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
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
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 😂
Did anyone ever figure out the measure (time 11:12) for number of units?
=SUM( TimeBasedData[Units] )
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