Time Intelligence in Power BI

expertiseexcelerate

Time Intelligence in Power BI  Unlock the full potential of your data analysis with our detailed guide on creating and utilizing a proper Dates Table in Power BI. This playlist is designed for professionals, data enthusiasts, and Power BI users who want to ensure their reports are accurate and insightful. In this course, we emphasize the importance of a well-structured Dates Table, showing you step-by-step how to create one that enhances your data models and reporting.  Whether you’re a beginner or a seasoned analyst, mastering the Dates Table is crucial for time-based analysis and accurate data interpretation.  What You’ll Learn: • Why a proper Dates Table is essential for Power BI • Step-by-step guide to creating an effective Dates Table • Best practices for integrating the Dates Table into your data models • Tips for optimizing time-based analysis and reporting accuracy  Ideal For: • Data Analysts and Business Intelligence Professionals • Power BI users looking to improve their data model accuracy • Anyone interested in mastering time-based data analysis  Subscribe to keep up with the latest software tutorials and ensure your Power BI reports are built on a solid foundation with a proper Dates Table.  Power BI time intelligence tutorial Time intelligence functions in Power BI Mastering time intelligence in Power BI Advanced Power BI time intelligence techniques Time intelligence calculations in Power BI Time intelligence best practices in Power BI Exploring time intelligence in Power BI Power BI DAX time intelligence Creating time intelligence reports in Power BI Power BI time intelligence tips and tricks  #PowerBI #TimeSeriesAnalysis #BusinessIntelligence #TimeIntelligence #DataVisualization #PowerBIDashboard #DataModeling #DataAnalysis #PowerBITutorial #BusinessAnalytics #PowerBITips #PowerBITricks #PowerBICourse #PowerBI2024 #DAX#PowerBIforBeginners #MasteringPowerBI #AdvancedPowerBI #PowerBITipsandTricks #LearningPowerBI #DAXPowerBI #Dashboard #Reporting #PowerBIFeatures #learnPowerBI #Analytics #MicrosoftPowerBI #DataScience

Transcript

00:00Hey friends, welcome back.

00:07Now in the last video at the end, I reopened the Power Query editor to show you that we

00:12can dive back into it and continue cleaning and transforming our data, but also because

00:18our model setup is not finished yet.

00:22What do I mean by that?

00:23Well, that is a good question.

00:24The issue here or what's missing currently is a dates table.

00:29Dates table is something very, very important in Power BI when it comes to any kind of time

00:34intelligence.

00:35Time intelligence means, for instance, you want later on in your reports, compare the

00:42this year sales to prior year sales, for instance, right?

00:44Or this month's sales to prior month's sales.

00:47So any kind of time calculation based on the measures you have in Power BI, they require

00:52a proper dates table.

00:55And for that, I have something which you can download from the resource section and follow

00:59along, which is a custom M code.

01:02M is the query language inside the Power Query editor.

01:07What's meant by that is, let me just show you, let me just open the code I have prepared

01:12for you, which is this one here, okay?

01:15So I totally get that it looks kind of intimidating, at least at first when you see it the first

01:20time.

01:21But what it actually is, is a function written in M. And this actually, all these codes,

01:28so this M code, this is also created for all the transformations we do.

01:33So let me just quickly show it to you.

01:35So for instance, for the sales here, this transformation we have done by combining these

01:40three tables under the hood, actually M code was generated in order to do this.

01:47So we use the front end here, so we can simply click on specific transformation steps.

01:53But under the hood, what Power BI is doing, it is executing M code.

01:58And if you would like to see what kind of code it is to create the sales, you can simply

02:03go under home to advanced editor.

02:06And now you see what actually was the formula which Power Query used in order to create

02:11the table.

02:12So there's a function in M which is called table.combine.

02:16And then it requires inside curly brackets the name of the tables.

02:22Now all these codes here, which the code is generated, you do not need to know it.

02:28I mean, if you would like to dive deeper into M to write your own M code, you can do that.

02:33But to get started with Power BI, this is not required.

02:37Because the transformations are done for us by Power BI and the complexity is abstracted

02:43away because we can simply click on specific icons here to do transformations.

02:50But it's great that you can see that, for instance, these applied steps, source, navigation,

02:55change type, and so on.

02:56If you select a table, you go to the advanced editor, then you see that what actually gets

03:01executed under the hood.

03:03So a connection to the Excel workbook, in this case, the selection of the source, the

03:08transformation of the data types of the columns, all this is in here.

03:12So if you like to see this and like to learn more, then this would be a good option to

03:16simply select the table of transformations and then go to the advanced editor to take

03:20a look at that.

03:21But if you do not want to do that and it looks quite complex, no worries.

03:25Because as I said, there is no need to do it.

03:28You have the interface and we can do all the complex transformations in here.

03:33For a dates table, however, we're going to use something which is already prepared.

03:37This one.

03:39And don't worry, it's quite simple because all we need to do is simply copy everything.

03:43I press control A in here, control C to copy it, and then I'll go back to my model in here

03:49and I'd like to add a new source.

03:52So I click on under home, new source, and then I create, instead of connecting to a

03:57specific file, I go to blank query.

04:01The blank query means that, again, I go into the advanced editor, which I've already shown

04:06you, and here I can write my own query.

04:11And there's a default syntax inside.

04:13I remove this and then I just control V to paste everything I copied.

04:20It's quite a lot, but it creates a lot of columns for us.

04:25So you see that no syntax errors, which is good.

04:28So let's click on done.

04:30And now what happens is a function gets created in here.

04:34This function requires a few parameters like the start date, the end date, and the financial

04:39year start month.

04:40Now, in our case, we have three years, which is 2021 up to 2023.

04:47This is why as a start date, I can simply type in here, that's the first January of

04:532023.

04:54So first, and the end date is, oh, sorry, 20, be careful, 2021, of course, the end

05:03date is in this case, the 31st of December in 2023.

05:10So this makes sure that I have the data for all my three years.

05:14And then if I would have a fiscal year, which is different from starting in January, for

05:20instance, my financial year starts in April, anything like that, or in May, then I could

05:25actually add the month number in here.

05:27For us now, it doesn't make any difference.

05:29So I simply type in a one.

05:31And if you have a holiday calendar, you could also choose the holiday calendar, then this

05:35holiday would also be implemented.

05:38I don't have this, so I just give it, but it's also available inside the M code, which

05:43you can again download from the source section.

05:45So this one I've shown you, this one, okay.

05:49So we copied it, we specified the date range we want to create, and everything we need

05:55to do now is simply click on invoke.

05:58And now we have a complete new table created, this called invoke function.

06:02So let's actually rename this properly and say, this is our dates table, call it dates,

06:07press enter.

06:08And now we have the dates.

06:11And what happened now is that all of these columns in here have been created with this

06:16M code first.

06:17And you see that we have a date column in here, which is starting in 1st of January

06:222021, up to, of course, then the 31st of December 2023, because that's what we specified.

06:29And you can see that all the columns we have in here, we have here also the month names

06:34in here, we have an abbreviation with the month name and the year, we have here the

06:39weekday names, even though they are in this case in German, because it's my native language.

06:43But of course, for you, it would have shown in your native language, and all the other

06:47kinds of fields in here.

06:49So you see, there are a lot of specified fields, which you can use in order to slice and dice

06:53our data.

06:54But much more important than just slicing and dicing is that this table here allows

07:00us to do time intelligence in Torbjörn when we create our first measures.

07:04So as I said, a comparison between different years, for instance.

07:09And the reason why it is best practice to use a separate dates table like we do here,

07:14instead of relying on a date column, which we already have in a model.

07:19For instance, in sales, there is, of course, also an order date column, right, this one.

07:24Now, of course, we could try to use the order date column for our time calculations.

07:29But this would not be a good idea.

07:31And the reason for this is, we cannot make sure inside the model.

07:36So let me just take this inside the model that we have actually reflected each day in

07:41this data set.

07:43So for instance, here, I can see it starts here with the 23rd of January, sorry, of February

07:492023.

07:50And then I have the 24th.

07:52But for instance, what if the 25th is missing?

07:55So I cannot guarantee that on each day, in our three year period, I have a sale.

08:02And because I cannot guarantee this, that also means that the order date that might

08:07have missing dates, because maybe we have a date, and on this specific day, nothing

08:11was sold.

08:14If our calendar is not complete, so has missing dates, then there might be the case that the

08:21DAX, which is the Power BI query, Power BI language, when we create our formulas, that

08:26the DAX calculations are wrong.

08:28So they give wrong results.

08:30So we need to make sure that we have a proper dates table, where no date is missing in the

08:35date range we want to analyze.

08:37And only then, we can ensure that our calculations are always correct.

08:42And that's why it's so important to have a separate dates table, because with this dates

08:46table here, which we created, we are ensured that no date in here is missing.

08:52And this allows us then to do or base our calculations on this specific column.

08:57That is why it's the best practice to have this separate dates table created to make

09:02sure that we have perfect all the dates, okay?

09:07So then, let's actually drag this also inside our model view, because I want to use it in

09:12here.

09:13So that's fine.

09:14And for the query, let's actually drag the query down, because query itself was just

09:18for creating the table.

09:19We don't need it anymore.

09:20And it's not part in here of the model itself.

09:23For the query, there is no need to right-click and somehow disable the load, because the

09:29query itself is just a query.

09:30It doesn't create any table, as long as we do not create some kind of parameters here

09:34and invoke it, right?

09:36You can also see it simply by the icon, it's simply a function, right?

09:39It is not a table itself, so there's no need to enable the load or disable the load on

09:44that.

09:45Okay, so that's it for the dates table.

09:49So again, I'd like to mention, this is very, very important, okay, regarding any kind of

09:53time calculations and time-based calculations in Power BI and DAX.

09:57You want to have a dates table which reflects all the dates, so there are no missing dates.

10:01That's very, very important, okay, to ensure that the calculations are correct.

10:06So if you can guarantee that in your native data you already have, and there are all the

10:11dates inside, you might also go with this one, but as I said, as a best practice and

10:16for all the projects I have done so far, I always use a dates table.

10:22And just to mention this, maybe you'll see this in the future, the dates table itself

10:27does not have to be created inside the Power BI editor with this function, there are also

10:31other ways to do it.

10:33There's also an option even to create a dates table with DAX formulas in Power BI, but in

10:38this case, I'll just use this formula here and create a dates table this way, okay?

10:43So as always in various products, there are always more than just one way to skin the

10:47cat, but this would be one way.

10:49And the great thing here, I'd also like to point this out, is you can reuse this formula

10:54all the time.

10:55So not only for this specific course here, but also for all your future Power BI projects.

11:00You can simply use the code we have seen, go here, just ctrl-c, ctrl-v to copy it, paste

11:06it in here, as in this case, under new source, go to blank query, and paste it there inside

11:11in the advanced editor, and then specify the date range you want to analyze, and then use

11:15it in the project.

11:16So at the same time for all the projects you have, okay, there are no other things to do.

11:22So hopefully that is helpful, and that's it for this video, thanks a lot for watching,

11:27and I'll see you in the next one.

11:29Until then, best guys.

Category

Transcript

Recommended