Time Intelligence Dates and Drilldown Functionality in Power BI

expertiseexcelerate

Time Intelligence Dates and Drilldown Functionality in Power BI   Take your Power BI skills to the next level!  In this comprehensive Power BI tutorial, we’ll guide you through the essential Date-Time feature and show you how to create dynamic date hierarchies that allow you to analyze your data across various time levels — from years and quarters to months and days.   What You’ll Learn: • How to enable the Date-Time feature in Power BI for better time intelligence. • Step-by-step instructions to build custom date hierarchies for more insightful data analysis. • Master the drill-up and drill-down features to explore your data across different time periods with ease. • How to leverage Power BI's time intelligence features to visualize trends over time. • Tips to effectively use Power BI's built-in time filters to analyze data at various time levels.  Whether you're a beginner or an advanced user, this tutorial is designed to make you a Power BI pro when it comes to time-based data analysis. Watch this video to learn how you can drill down into your data with precision and make the most out of Power BI's powerful time intelligence capabilities.   Chapters: 0:00 – Introduction 1:25 – Enabling the Date-Time Feature in Power BI 3:45 – Creating Date Hierarchies (Year, Quarter, Month, Day) 8:30 – Drill-Down & Drill-Up Techniques for Time-Based Analysis 12:10 – Analyzing Trends Over Time with Power BI 15:45 – Best Practices for Time Intelligence in Power BI   Don’t forget to Like, Share, and Subscribe for more Power BI tutorials!  Power BI Time Intelligence Date Drilldown in Power BI Power BI Date Functions Advanced Time Analysis Power BI Power BI Hierarchical Date Drilldown Creating Time Intelligence in Power BI Power BI Calendar Table Techniques Optimizing Time Intelligence in Power BI Power BI Time Series Analysis Using DAX for Dates in Power BI  #PowerBI #PowerBIDrilldown #DataScience #BusinessIntelligence #PowerBIDates #PowerBIDashboard #DataVisualization #DataAnalysis #TimeIntelligence #PowerBI2024 #DataInsights #BITools #PowerBIforBeginners #PowerBIFeatures #PowerBITips #PowerBIReporting #AdvancedPowerBI #PowerBITraining #PowerBITutorials #PowerQuery #PowerBIDrillthrough #2024 #TimeSeriesAnalysis #DataModeling #Drilldown #PowerBITutorial #Analytics #MicrosoftPowerBI #DAX

Transcript

00:00Hey, and welcome back. Now, let's dive deeper into dates in Power BI. Dates, of course,

00:11are very important in a lot of organizations because most often we like to compare, for

00:16instance, any kind of KPI, like sales, like profit, whatever, for specific time periods,

00:22for instance, right? So, to do this, we have created our calendar. And I told you that

00:28it's a best practice that you always have a separate dates table because this allows

00:32us then to do also time intelligence calculations and so on. But before we dive deeper into

00:38any kind of time intelligence, for instance, comparison between years, let's do that on

00:43a visual level first. So, let's create some kind of visualization where we can analyze

00:48the date. Now, by default, you should see when you open your dates table here, the date

00:54column in here, and you also should see a little calendar icon. Currently, that's not

00:59enabled for me. And the reason why it's not enabled is because I disabled auto date time

01:05feature. So, because I want to tell you what this actually does. By default, if the auto

01:12date time feature is not enabled, like this one, then the date column returns every single

01:18day. So, let me show this to you if I click on the plus icon here. And by the way, I want

01:23to mention this, if you have not done so far, please click on the disk or press ctrl

01:27s just to save your report. So, you do not lose any kind of progress you have done so

01:32far. So, back to our dates. In the dates table here, the date, if I would use it this way

01:39as it currently is, and I take this option, like that, click on it, I would see here a

01:45list of each individual date. So, also the names are in German, but believe me, this

01:50is Friday, the 1st of January, then there's Saturday, the 2nd of January, and so on. So,

01:55it's on a daily level, right? There is no hierarchy. There is no year or anything like

02:00that. There's just the specific day. So, the 1st of January in 2021. However, when the

02:09date time feature is enabled, let me just enable it because you probably have it enabled

02:14and it is normally enabled by default. You can go to file and there's actually where

02:18you can check it, go to options and settings, go to options, and under options, when you

02:23go to the data load of the current file, take this option, there is the so-called time intelligence

02:30here and there's the auto date time feature. So, if you hover over the icon here, you get

02:35some additional information, automatically create a hidden date table for each field

02:40in the model that has date or time, data time. So, let me just enable it for now, just click

02:46on it, and then watch what happens with the date here. Currently, there is no calendar

02:51icon, but now if I click okay, now you see that we have a calendar icon in here. Now,

02:59the difference is the following. You can see there's also a little error here and if I

03:03take this option, you see that there's a hierarchy now attached to that. And that means if I

03:08now try to use the date here, let me click on date, you see that now I have a hierarchy.

03:13I have the year, the quarter, the month, and the day. And, for instance, if I now,

03:20these are separate values in here, and now, for instance, if I add one of our KPIs, let's

03:25do user measure here, let's use tone sales, and either click on add data or drag and drop

03:31it directly in here. They both work, right? So, we can also see here some kind of value.

03:36And let's maybe, instead of showing it like that, let's actually convert this to a bar

03:41chart. So, let's use a bar chart here, click on this cluster column chart here, take this

03:45option, and now you see that we have here a yearly basis here. So, and this is in complete

03:53contrast to what we have seen before, where we are on a date level. Because on a specific

03:59date level, we will not have the option to have this hierarchy, which we currently can

04:03see here. We can see that currently we only see the years, right? 2021, 2022, and 2023.

04:12And because of our hierarchy, which you can see here, which is automatically created because

04:16of the auto date time feature, we have also now these arrows here on top of the visual.

04:22So, for instance, if I click on this one, go to the next level in the hierarchy, click

04:27on it, and now you see from the year level, we are now drilled down to the quarterly level.

04:33We can see quarter 1, quarter 2, quarter 3, and quarter 4. And if we drill down further,

04:39we can go in here to, in this case, the monthly level from January to December. And if we

04:45drill further to the lowest level in this case, which is day, I can click this option,

04:50and now we are on a daily level, where we can see each day until, in this case, the

04:5431st. You can either go here and drill up, or you could also, of course, use this arrow

05:00to drill up. Now, you might ask yourself, well, wait a minute. We have three years of

05:06data in our data set. And currently, I can only see 31 days. What is that? What does

05:13that mean? Well, that's a valid question. The point is, currently, because we drilled

05:18down, we can see this here. So, each day in our data set, but this is aggregated for all

05:24the years we have. So, for instance, this one here, this day number one, is the first

05:29day of the month, but for all the data we have in the data set, so across all years,

05:34across all months, it's just the first day of each of the months combined. And the same

05:40is true for all the other dates, all the other days, right? That's why we have 31 days. So,

05:46let's actually drill up. The same is true here for these, for instance. This currently,

05:51this January sales data here, where we can, if you hover over it, you can drill up or

05:56But this data here reflects the sales for January for 2021, 2022, and 2023 combined.

06:05So, that's what this hierarchy currently is. So, let me drill up again and drill up again.

06:09And now we are on the highest level. So, maybe sometimes this is something which is interesting,

06:15right? Because we could then clearly see, for instance, okay, if we only want to analyze the

06:19monthly level across all the years, maybe there's a specific month where we always sell more than

06:25the other ones, right? That could be an insight. But most often, you probably like to see all the

06:31months for each year individually. So, then you can also drill down, but you don't use this option

06:39with the double arrows. Instead, you use this one here, which expands all down one level in the

06:45hierarchy. Meaning if you click this one, you'll see that now we can see here the various quarters,

06:52but this time also for each of the years. So, this one, this Q1 is only the first quarter

06:58in 2021. And this is the first quarter here in 2022 and so on, right? So, that is the difference

07:06between those two arrows. And if you go another level down the hierarchy, now you can see each

07:13month for each quarter in each year. So, this time, this is really only January sales in 2021.

07:20And then, of course, you could drill down further, but this, of course, then,

07:25well, needs a lot of space. But if you want to do that, you could also go down here

07:28on level and then now you're on a daily level, right? As you can see, now you can scroll a lot

07:33across the three years, but this would be really on a daily level regarding, in this case, the sales

07:37KPI. And of course, you can add additional KPIs here as well, or replace the current one if you

07:43want. So, these are the options you have with the hierarchy in case you have enabled the order

07:50date time and this hierarchy is automatically created. So, let me just go up again. So,

07:56let me drill up, drill up and drill up. And let me show you the last option you have

08:01using the drill functionality in here. You can click this one and now this is selected

08:08because it's highlighted here in black. And this allows you to drill down for the specific,

08:13in this case, bar you click on. That means, for instance, if I now would click on 2022,

08:19if I click on it, you see that now I see the quarters Q1 to Q4, but only for the year I have

08:26clicked on. And if, for instance, now I would select here the second quarter, click on this.

08:32Now I can see April, May, and June. So, the second quarter, but only for 2022.

08:39So, this drill down functionality here, if you enable it, allows you to drill down

08:43to the specific bar chart, in this case, the bar you have selected.

08:48And if you want to go up, it works the same way. You just go to drill up, or you could also hover

08:52over this, right? And then use these options in here. Let me go up again. Let me go up again.

08:58Okay. So, this is when you enable this one, then you give the end consumer, of course,

09:03the option to click on a bar to drill up and down, right? Because these arrows here,

09:09these are also available for the end user of the report. If you publish a report,

09:13just mention that. So, that's it for dates. This is the options you have to drill down

09:20if the hierarchy for the date is enabled. Now, if the hierarchy is out of the time,

09:27it's not enabled, then what you have normally is, currently you can see here the hierarchy,

09:32and you hopefully can see that you can also remove elements from the hierarchy.

09:36So, for instance, if I say, I don't like to see the day, I could simply remove the data here.

09:41And now, if I try to drill down, I can do that, right? I can drill down here, and I can drill

09:45down further. And now you can see, I cannot drill down to the day anymore, because I removed this

09:49element from the hierarchy. And the same is true if I were to remove the quarter, then the year

09:54would drill down to the month and not the quarter, because this is then removed. So, you can specify

10:00how you want to use it. So, let me just maybe go up again. Let's go up to the top and also remove

10:06this one and this one. And now you only have the year, and you can see that now you can't drill

10:10anymore. And by the way, if you would like to have the same behavior as when the hierarchy does not

10:17exist, you can also get this by right-click here and say you would like, instead of using the

10:23hierarchy, you would like to use the date. So, if you click on date like that, then this is what you get.

10:30So, you can see that clearly is different from what we have seen with the hierarchy for the visual,

10:35because now, again, each specific day is plotted here as a bar and then filled, of course, also in

10:41this visualization without any scroll bar. And this year, this visual is the default behavior

10:48when you do not have the order date time enabled and you don't have a hierarchy.

10:56So, that's why it made sense to have this order date time enabled.

11:01However, when you're dealing with really big reports and huge data, then the order date time

11:06is actually a very bad thing to have, because you have read it, maybe, but let me remind you,

11:12what it told us was it creates a dates table. So, under the hood, so a hidden dates table

11:18in the model for each column you have in your complete model, which contains dates.

11:25And this means that the model, the Power BI file, can get quite large. This is why normally you like

11:31to avoid having the order date time feature enabled if you have really big files. So, if

11:37performance is not an issue, then leave it enabled and you have the hierarchy automatically for you

11:42and you can use it in your reports. If not, so if you've got performance issues, then disable

11:48the feature and create the hierarchy yourself, because that's also possible in Power BI.

11:54And if you want to switch back, always, as long as you have the hierarchy enabled,

11:58you can see the calendar icon, you can right click here and also switch between those two,

12:01right? Also, date hierarchy again, and you have the hierarchy.

12:06And by the way, this also works, for instance, if you say you remove quarter in month and then

12:11you decide, well, I want to get my month back, just right click here, say I want to go to date,

12:16right click here, go to date hierarchy, and I have all my hierarchy that is in here back,

12:20right? That should be a faster way than just redrag it in here, maybe. Okay. So, that's it,

12:27actually, for dates and times. So, hopefully, you can use this in your reports. And as always,

12:32I encourage you, try this out, play around with it, drill down, drill up, play, use these features,

12:38maybe feel free to use a different chart type, and then I'll hopefully see you in the next video.

12:44Until then, best guys.

Category

Transcript

Recommended