Up next in 10
Working with time intelligence DAX functions by Parv Chana || Lightup Conference
14K views
Nov 16, 2023
For any BI report, time-based analysis is a key part of the report. For example YoY MoM, QoQ comparison, and many more. There are around 44 Time Intelligence DAX functions. In this session we are going to talk about: - Why add Date Dimension in your model? - Why Date Dimension (Table) is important? - What are the data model design practices to follow? - When to use DATEADD vs DATESINPERIOD vs DATESBETWEEN DAX functions? and also will go thru a few real-time examples using time intelligence functions. Conference Website: https://www.2020twenty.net/lightup #lightup #2020twenty
View Video Transcript
0:26
quickly
0:30
Again, this is just from the from the organizer. Donate, scan this link and it's in the support of UNICEF
0:41
I guess you guys already seen in the other presentations as well
0:46
Again, these are the sponsors for this awesome cause. How are we doing here
0:55
Kudos to all the sponsors and just want to share that as part of my presentation
1:01
And super glad to be here sharing my. This presentation today with you guys
1:09
Alright, quickly on agenda and we're not going too much in detail
1:13
but told a couple of things we're going to talk about is what is a date table
1:19
also known as calendar table most of the time and why it is important
1:24
What are time intelligence functions actually? What those are? You know what does that actually means
1:32
And we're going to also look into some of the examples. About the time intelligence functions, we will go some debug techniques
1:41
depending on the time we have only 45 minutes to try to cover as much as I could
1:48
and best practices and summary and where to go and learn more
1:52
Um, to play about to me, my name is Pervinder Chana, also short Perv Chana
1:59
I'm a BA architect and consultant. I have my own consulting company here in Canada, Vancouver
2:05
You can reach me at LinkedIn, Twitter. I'm not very active on Twitter, but LinkedIn, I'm very active
2:12
Of course, I do write some blogs and try to stay up to date with my knowledge and
2:17
and of course you can send me an email here as well
2:20
OK, so to the topic. So what is a data cable and why it is important
2:28
I know the topic says the time intelligence, but the fundamental and the basic need
2:35
for a time intelligence functions to work, we need a data cable in our model
2:41
So why data is important? Well, first of all, there are around 44 plus I think in 44 or maybe more
2:51
There are time intelligence functions like, for example, dates, year to date, previous year, previous month and date at same period last year
3:03
Parallel period. You guys probably seen all these DAX functions. Those are known as a time intelligence function, basically which are working on a time series and
3:14
giving us allow us to create different kind of ysis like trend ysis, you know, comparison
3:21
year over year, month over month and whatnot. To use all these functions, it is recommended to have
3:28
the date dimension table because that dimension table contains a special metadata which allows
3:35
these functions to work as they expected to work if we don't have the date table we have to
3:45
do some work around to make these functions work you can still make those work but not
3:49
efficient and effectively as compared to having a day table in your model
3:55
so then if that's important what are the requirements for the day table
3:59
Again, it's a simple thing you guys already seen historically in data warehouses
4:05
It's nothing new to the power BI. It's a traditional data warehouse star schema
4:12
If you talk about it, it's in a requirement where you have a date table which has the
4:18
continuous date. One of the requirement is there are dates. It could be a five year long table
4:23
one year long table six month table um 20 years depending on your wall your data start date or
4:31
end date whatever you're comparing is but the key is that there is no missing date so if let's say
4:39
if we take an example of 2019 we have a date table of 2019 2020 so basically we have a continuous
4:48
date starting from first january 2019 to 31st december 2020 and the date is unique in that table
4:59
it's not like you have a a duplicate date dates uh record and then you have more attributes like a
5:05
year month month number and whatnot i think everybody who work with rbi or working with
5:12
power b are learning all the stuff they uh know all that stuff and if they don't i would highly
5:20
recommend one must have a data dimension in their model ah so that's that's why the date table is
5:26
important because we need to use take advantage of those time intelligent functions available
5:31
in uh dax functions available uh for us to use um what are time intelligence functions um you know
6:08
That means he's referring to one of those function of Dax functions available to us
6:16
So basically it's a common name to deliver result like year to
6:21
date running to. Same period last year, drill down from year, quarter, month
6:27
week, date, whatever you name it, fiscal calendar and whatnot. Again, this particular session I'm not going to cover about the
6:35
and whatnot because that's a totally different topic. But all these functions are grouped together is called the
6:42
time intelligence functions. That's what it is all about. And we're going to look into these functions depending on the
6:50
time where we will try to cover as much as we could. Again, I'm going to quickly switch over to couple of the
7:22
right now not the presentation right Yes yeah OK Perfect thank you Perfect Okay Yep OK sounds good Alright here I have a very simple
7:35
power be a report and. I just want to make sure a couple
7:41
of things I want to show here. So I have a one sales table here
7:47
Nothing fancy and it has around 60,000 rules in it and you know
7:53
you know, a typical sales table or sales order table, it has an order date, it has a due date
7:59
and it has a ship date. So multiple dates, of course, to yze if you want to see how many
8:04
orders we shipped in this month, how many orders are due in this month, and how many order we
8:09
received. So all these dates are there for us to do any kind of ysis. Now, when you look into
8:16
these this table inside um what you see here is each date column has a date hierarchy underneath
8:26
this we never told uh created or asked for this hierarchy but it is there um now we will talk
8:33
about where it comes from and whatnot so what this hierarchy does is it has a year quarter month day
8:38
similar other date columns for our order date, same thing and also with our ship date
8:48
Where is this hierarchy coming from? Power BI wants to bring your data in the model
8:55
Any date column, the type of the column which is date, it creates a hidden date table behind the scene
9:02
for that particular column. So in this case, It has created a date table behind the scene with the minimum
9:10
and the maximum date range based on the due date. Date range in the due date column and it created a full
9:21
Let's say if the due date start from a 2018 and until today it
9:26
will create a date column date table starting from 2018 to until now and similarly for the order date and the ship data as
9:34
So basically we have a 3D table. Now the problem, this works great if I really want to
9:42
I don't need any date table in my dimension because it's already built
9:46
But if I want to see my sales order received and the due or shipped on the same common x-axis, I cannot do that
9:58
One of the reason is because there is no common date cable in this particular model because all these columns has their own date table
10:10
I can surely, in this case, I've taken the due date year, quarter, month and on the values I put the sales amount, I can drill up and down the way I wanted to see it
10:21
but I cannot see how many orders shipped in 2011 or how many orders we received in 2011
10:28
To do so, I need to use the date table associated with that specific column
10:35
So that's the one challenge with this particular model. If you have multiple date columns in one table or other tables as well
10:43
you don't have a common dimension where you can slice and dice the data
10:47
in terms of year over year or month over month or whatnot
10:51
The second thing is now we have multiple date columns. It really grow our model very big
10:59
Instead of having three date tables behind the scene, in this case Power BI Automated created
11:05
if we create our own date table and connect with or set the relationship between these columns
11:12
we will have one date table. And instead of multiple date tables, plus we will be able to use
11:18
that common date table to slice and dice the data across different measures what we want to use
11:25
and that brings us to one of the best practices like I would highly recommend to disable you can
11:34
actually disable this date table in your model and and create your own so that Power BI does not
11:42
create its own internal data table and I have seen a models
11:47
where there are like, let's say 20 different columns in 20 different tables. All the date columns and the size of the
11:55
model is very, very big. I seen sometimes people say you know
11:59
what my my table my power BI model was very small. I added new
12:03
cable and all of sudden my power BI. I have only 100,000 rows
12:08
but my power in my model has grown from increased by let's
12:12
say 100% or 200% and what happened is probably in that new
12:17
table you brought in the model has to your four or five maybe
12:22
date columns and behind the scene it created these hidden tables and your model size has grown whereas you see that only you
12:30
brought one table and and the model size has gone so big
12:34
In fact, I was recently working on one of the model. It was around 1.5 gig and there was no date dimension
12:42
They have like around 20, 25 different date columns. And the model size was crazy big
12:49
And once I brought our own day table, disabled our auto-date timetables, which we'll take a look
12:59
and the model size went down almost 50%. actually it's not big and here is what I'm going to show with the similar thing in this particular
13:07
example the same sales table with around 60 000 tables rows and three date columns internal date
13:15
has created table has created I have the similar um same um um
13:25
Power report the sales table. Similar if we see we have a sales
13:30
table here. And we have same three columns we have same 60,000 rows
13:37
No more. Data hierarchy here as you can see in the date due date and order it
13:43
How you turn that off? You go into options and settings. You go into options and
13:51
You should always turn this off and this is where you say
13:57
time intelligence auto day time off. This is per report based, but if you want this to be global, of course you can go to
14:06
data load in the global setting and you can turn it off. What
14:09
does that mean is every time you start a new report and create
14:13
a new report and you bring a table into the model which has
14:17
it will not auto create those hidden tables. So that's how you disable those dates
14:23
And then people I see when when this happened, I recommend OK to turn that off
14:28
They say well, we lost a hierarchy with those of my hierarchy or before that I
14:32
year month date the hierarchy and I lost that. Again, as I said
14:37
you need to bring your own date table. So in here this in this particular example
14:42
I created a date table using the DAX function. I have a blog post on this which you can look in my website
14:50
There are many blog post great blog post on how to create the dimension table again
14:54
Not it does not matter you create through DAC so you create through power query or you create through if you connecting to your back in SQL Server system or any other system it has the date dimension
15:06
already in there you bring it from there it does not matter what matters is that you have to have
15:12
a date dimension table in your model and a couple of things you need to do is once you bring the
15:18
date dimension table you make sure that it is marked as a as a date table what does it do is
15:25
at that point of time um mine is already marked uh uh let's say i unmarked it so if i go back
15:34
and mark as a date table uh let's go back mark as a date table select a column our date is the of
15:44
course the requirement is select a column to be used for the date doesn't need to be the name
15:48
date could be anything but it has to be a data type date and only unique values so you choose
15:55
your column and it validate and then it mark as an a date table what does the markers date table
16:00
does it has some special metadata behind the scene which is used by the time intelligent functions
16:05
there is a there's a link in my slide presentation um once i share with the with the organizers and
16:14
that they share it with you. You guys can take a look at the link
16:18
and see what does the mark as date table does. So what I recommend is always my way of doing this
16:25
is I have created a template which has a date table in this created in Power Query
16:29
Any new report I start, I start with that. It already have the date table in it
16:34
And then I work from there. The point here is to bring that date table
16:42
Like how does it make a difference in the model? So here is one comparison I have done
16:47
with these two reports which I showed you, one earlier and one now, this one
16:52
So this one, auto-date time off, we turn this off. And my file size is, size of the model is 493 kilobyte
17:01
with the two tables only. And the one which we saw before
17:07
we had the auto-date time on. And look at the 60,000 columns, the rows only in one table
17:14
The model size were 2.2 megabytes with the six tables. We couldn't see the six tables in the model, as I said
17:21
if I quickly go back to my old one, we don't see six table in this particular file
17:30
But what we see is only one table, which is sales. But through the DAX Studio
17:36
through the VertiPack Performer yzer, Looking at this, it says six tables
17:41
So what does the other tables are coming from those the dimension hidden tables
17:46
And the difference between these two is approximately 75% reduction in the size of the model
17:53
And if you're loading the model in the memory and think about that, how big of an advantage this is
17:58
and it's only one table, three date column, and 60,000 records and such a big impact
18:04
So point here is whenever you start in a Power BI report
18:09
And of course, if it has anything to do with the dates
18:14
you know, comparing year over year, month over month, anything even visualizing the data
18:19
slicing year, month, date, turn the auto date time off, bring your own date table, mark that as in a date table
18:26
and use that as your visualization, a common dimension to slice and dice by year, month
18:36
or quarter, whatever your needs are. Other question comes is usually, you know what
18:43
I have a common date dimension table now. I have a sales table
18:48
I can't have a multiple relationship because we have one common dimension table here, date
18:54
And we have a sales table, which has three different dates. Now how we handle that
19:01
because this doesn't have a separate date dimension now anymore. So you can surely have more than one relationship
19:09
between your date dimension table or any two tables. This is nothing to do with in a date dimension
19:14
but any two tables, but in this context, is more about the dates
19:19
You can have the relationship and then in your, because between two tables
19:23
you can have only one active relationship. In this case, our active relationship is date and the order date
19:30
So it means anything I slice and dice on the date for the sales
19:35
it will be on the order date. but these are the inactive relationship
19:41
What I need to do is to keep these relationship active. I just need to create the measures
19:47
And in the measures, I need to use the function called use relationship to make this relationship active
19:54
So what does that means is in that specific measure, which I write, I can say, make this due date
20:03
relationship active. In this case, I can say ship date as an active
20:08
So then you can on the same X axis, whether it's a year, month or quarter
20:14
you can see the number of orders for the same month and number of orders shipped and number of orders due
20:21
on the same common dimension instead of having the multiple different date tables or the hidden table
20:29
So people often ask me like, okay, you know what? if I turn that off, I can't make this work
20:36
But yes, you can have multiple relationship and make those relationship active in your measures
20:46
So those are the couple of best practices. I want to also talk about other things people talk about
20:57
like year over year, quarter over quarter, and all that running total and whatnot
21:02
you know, those functions are sometimes, or how to use those functions people don't know
21:08
I always recommend you start with the quick measures. It's an easy way to learn about the time intelligence functions
21:16
If you don't know where the quick measures are, here I'm going to quickly walk you through
21:20
So what I have here is in this particular visual, we have the month name coming from
21:28
if you go here from our date dimension on our columns, we have year and we are seeing the sales
21:38
So basically we're seeing January sales for each year and February sales and whatnot
21:44
Now, if we want to see the year-over-year comparison, it's pretty straightforward in my sales
21:52
If we look at the sales my year, it's a pretty sum of the sales amount
21:57
So if I click on this three dots, so we have new cook major
22:05
So while we have a new cook my years, I'm pretty sure you guys already seen that this is the best way to learn about the time intelligence function
22:14
So let's say, for example, year to date, quarter date, year over year change, quarter over quarter
22:19
So everything is in there. So let's pick year over year change
22:24
And of course it going to ask OK what is your base value So this is our major which we just created some sales amount and the date again it goes back It needs a date the date field over which
22:36
to calculate the change. So of course, our common dimension is date table and we will pick the
22:43
date column from there and number of periods you want to go back. If you want to compare
22:49
let's say we're in 2020, we want to compare with 2018, we will say we want to go back two years
22:55
If we want to compare with the previous year, which is usually the common scenario, we will pick the
23:01
number of periods, go back one period. So what does that mean is it's going to write a DAX for us
23:06
That's the quickest way to learn from the DAX what it has done. So if we look at the, this is the new
23:14
So my year it has created. I already had sales. Why you why year over year
23:19
So this is the two. So what it has created it's a variable
23:23
Of course, I'm not going to go into the tax too much here
23:28
So variable it's creating calculate sales. Did add this is again a time Italian
23:33
functions we talked about. Take the date column from the date dimension
23:39
Go back by one. What is go back one minus one. you can go forward or backward
23:45
So we're saying whatever the contacts I'm looking at, go back by minus one
23:50
And then what unit you want to go previous year or previous? So there's a multiple choices here
23:55
If we are competing year over year, it will be year. If it's month over month, it would be month
23:59
It's a previous day. It would be day over day, quarter over quarter
24:03
But in this example, we have year over year. So what does that means is it's gonna
24:09
looking at the context where we are, it's gonna go back by one year
24:14
and then take current sales, take previous year sales, and divide by previous year sales
24:19
So if we drop this measure here quickly, and we see the growth
24:26
now you can see, I guess let me maximize it. Collapse this guy
24:33
So in 2012, in January, we have 495, in 2011 in January 469 and 5.44% growth
24:44
Similarly, in February 8.72 and March. So writing that measure, it's automatically created that
24:52
Now, sometimes it's year over year, but I really want the absolute number
24:57
or the absolute difference. I can actually, rather than redoing everything, I can take the DAX expression here
25:05
create a new measure. And like this is this is easy to learn
25:12
I know it already done. I know the previous year is this calculation is already done
25:18
So what we can do here is. I want not to divide
25:24
I say sales minus previous year. Is the one that will be the
25:30
absolute difference between. Sorry, together year over year. Maybe we call it
25:38
difference so we use the you know from the cook my years which already wrote an expression for us
25:46
we took that and if i drop this in the um in the visual here and i have a absolute difference from
25:56
the previous year um of course there's nothing in 2011 there's nothing in 2010 so it's the
26:03
difference same but if you look into 2012 compared to 2011 so this is 25 000 increase from the
26:11
previous year 40 000 in terms of the dollars and in terms of the percentage so we took advantage of
26:18
cook my years so i would recommend a look into quick measures running total and whatnot
26:23
but the key is again going back to use those time intelligent functions that you have to have a
26:29
the dimension in your model to make these tax tax functions work um i'm gonna quickly uh i think
26:38
this was to just show the case like how the size of the model changes if you turn off internal
26:46
hidden tables and have a common dimension and um it's it's a no-brainer then why i want you to have
26:55
hidden tables in there. Again, we'll talk about the hierarchy. I lose hierarchy. You can create
27:01
your own hierarchy. In this case, I've created one that's very simple to create. You can just take
27:08
if I want to create quarter over month, so we can just drag and drop again. This is the basic
27:14
functionality in Power BI. You can create the hierarchy the way you want it to quarter month
27:18
year or in this case I created year month quarter month date and now I have the same functionality
27:27
to drill down like for example in this particular first page quickly I use that hierarchy here year
27:37
quarter month I can simply drill it down to quarters I can drill it down to months and
27:46
and to the date level as well. So so I can achieve the whatever the inbuilt hierarchy was there
27:52
I can create my own. It's more flexible the way you want. If I don't want quarter in this, I can get rid of that or I can get rid of my
28:00
hierarchy or the one in this particular use case. I can just let's see if I want year month and date only I can
28:07
I can use that. So that's kind of the fundamentals. one should always look into adding a date table
28:16
in dimension turn off auto date time and start from there. I'm going to start sharing my another power
28:22
via report here quickly so we have 15 minutes. I will try to go through some of the
28:29
some of the different functions here. Now one of the. Thinking time intelligence functions is like
28:37
there are many, many functions like for example, previous month, previous year
28:42
date add, dates in period, dates between so many of those. Sometimes it's very hard to see
28:50
which one is to use when and whatnot. So here is I have created a small
28:58
Comparing year over year just to give an idea here how it would
29:04
How does it work and how you expect or how you debug it actually
29:08
I always get stumbled upon when I'm looking at the things I'm like
29:12
what's going on? So what we have is I'm just gonna remove this hierarchy here and quickly
29:20
Sure this not to have this hierarchy here. Go to the date level
29:26
So what we have a date and I have like seven or eight different functions
29:31
So what are these functions? If I go to my my yet here quickly
29:36
Those functions are these are all again time and time engine functions. If I go to my dates function, so I have a date add
29:46
Panel period previous year same period last year function. So what has data is basically what I'm saying
29:55
In the max I'm giving me the maximum calendar date. Adding subtracting one year from the from the date dimension
30:05
from the date column and similarly I have a minimum so I'm using the date so I want to compare how these different time
30:14
intelligence functions actually work when you look at the different granularity. So it helps you to write your time
30:21
intelligence my years and whatnot. So data it's a simple adding one date I can go forward and not backward, but in this
30:29
because we are competing with the previous year. I just want to showcase that. So we're taking
30:35
this data that is giving um making our dates go back by one year and I'm whatever the result
30:42
set it returns I'm getting the minimum date from there and similarly in other example the
30:48
same thing date add taking the max. So other functions are parallel period the same thing
30:53
we have a parallel period function parallel period functions also can go backward and forward so it's
30:59
again negative one year and max calendar date um other one is previous year so so many functions
31:06
so that's why we get loss of previous year takes only one input some of these functions are sugar
31:13
coated to uh not to write too many parameters if they're the most commonly used so previous year
31:20
just take that calendar date as an act as in a parameter value as in function and doesn't need
31:25
to minus one and because it's always previous year so looking in the context the date it is always
31:31
going to give you the previous year and again what we're taking is a max and min and we have another
31:37
function is called same period last year it is also calendar date as in a parameter
31:42
it's taking same period last year and max and min so let's look how these functions work
31:48
differently so what i have on the x-axis uh sorry on on my rows so-called x-axis in this case um i
31:56
have a date column on my from the hierarchy because i just use hierarchy um i have a date so added
32:08
those four one two three four six eight functions same period last year min max previous year min
32:14
max parallel period min max date admin max so look at um how these functions work because sometimes
32:21
people say that it doesn't give me the result what i'm looking for when i'm at the looking at the
32:26
data at the date level let's say i'm looking at the january 1st 2018 the date ad returns me this range
32:34
first january 2007 to first january 2007 in this context so it's going back by one year
32:41
taking this date and going back one year because we told the date add function minus one year so
32:48
this is taking us to minus one year look at the parallel period what does parallel period does
32:56
if it's in minus one let's take a look at the function actually
33:00
parallel period parallel period minus one year similarly date ad is also minus one year but the
33:14
result set is totally different what does a parallel period give us it give us the whole
33:20
year even i'm looking at the first january 2008 it is still giving me the full last year so it is
33:29
giving me 1st January 2007 to 31st December 2007 whereas the date add is giving me the just the one
33:39
date whatever the last year compared to this date is it's giving me this this range and previous
33:47
year same it's giving me it doesn't look at the it says give the full date range of the previous
33:54
year in the context I'm looking at. So the previous year for
33:58
2008 is the full date thing. So these two words are similar, but
34:02
the same period last year gave us similar to the date add
34:08
Why I would use date add over the same period last year? If it is
34:12
always looking at the previous year because same period last year does not have It always last year Whereas the date add I can go back negative two I can go back positive one if I wanted to in the future year so it gives me load more flexibility depending on my needs
34:31
If I'm always comparing last year. Uh, of course the same period last year will work in this particular case
34:39
Now at the date level. So again, sometimes people say I use the parallel period
34:44
but when I'm looking at the date range, the numbers would be totally different because the range it is returning
34:50
is totally different than what we expect. So sometimes it's easier to debug it this way
34:56
I mean, create in your measures, create a measures with whatever filter you are applying
35:02
and take the min and the max date and try to see what date range you are getting
35:07
And because that is a date range, if I write a sales measure on this particular
35:12
using this function parallel period, even if I'm looking at the January 2008
35:18
it's going to give me the sales for the full 2007 calendar year
35:24
not for the January 2007. And as you can see, this is returning the full calendar year
35:32
And similarly, if I go, I turned off my hierarchy, but let's go show items
35:40
Sorry, I said show all levels. If I go up by now again, if we look
35:49
date add is interesting. Let's look into really done. Date add is always in context
35:57
This is in quarter 2008. It's giving me quarter of 2007, 1st January to 31st March
36:04
Whereas the parallel period is always, always because it's a previous year
36:09
parallel period minus one. If we look at the parallel period, It's always gonna because it's year regardless of what I'm looking at the quarter or the month or the date
36:18
It's always going to give me the full year. Similarly, previous year is always going to give me the previous year
36:26
But same period last year in the date add more dynamic. They are looking at this particular context and then returning me the range
36:36
If I go from the quarter to month level, so I'm January 2008
36:42
It is giving me in January 2007. Again, all these are giving me fully
36:47
here because previous year and the parallel period works like that and
36:51
the same period last year gave me that date range I'm looking at
36:55
So this is one of the debugging technique because when you're working with
37:00
these functions and you're working with the dates and whatnot and you don't know
37:04
Sometimes it's not look. Not what we expect. The one way to debug is just create a measure and give your
37:14
whatever you're filtering on in your DAX function of the money you're using. Use that and take the min and max and then see
37:23
what data you're getting in your model. In your in your measure. So here is like now you can clearly
37:32
how some functions even date add and we told go back one year parallel period
37:42
we go told it to go back one year a previous year isn't seeing Peter last
37:46
year is always previous year but depending on what range or the control
37:53
contacts you're looking at the data they return different different date range
38:00
Data is more data than simply lossy dynamic depending on the context. Whereas parallel period and previous year is always
38:09
returning the full month. And similarly we have the, you know, parallel period. I can of course if I say to I can always have
38:18
parallel period month, it will always give me full month regardless of even if I'm looking in the 15th of March 2008 and I have
38:27
parallel period, it's always going to give me full previous month. And similarly, we have the previous month as well
38:35
So you can use this kind of technique to find the range and
38:39
figure out what data you are working on and why your results
38:44
are not coming as they are expected. We have five more minutes Maybe I will come in a couple of examples here Let look at the previous month So here is one example I have
38:59
I've created pretty simple. So let's say it's pretty much what we have is
39:11
I have a sales major, which is simply a sales amount and
39:16
And previous year date ad is again calendar date, go back one month
39:23
I should call it previous month, not the previous year. And this one is a sales previous year
39:32
previous month, so it's a previous month basically. If we look at these numbers
39:41
let's pick one January 2011. So I will get down to the date level
39:48
Look at what happened here at the month level. My date add function is returning me 43,421
40:00
And if we go to January 21 is giving me full December 2010
40:09
because that's the previous month. and that's fine and here in this case also i am i'm getting the previous month 4341 but here it is
40:22
blank and for the date add function whereas in the previous month i'm getting the numbers and the
40:28
reason behind that is what we've seen in the previous table i showed you when i'm looking at
40:34
the first in january 2011 the data function is giving me december 2010 as the month uh and i'm
40:44
getting the december 2010 sales but when i'm looking at the first january 2011 what's happening
40:50
here is it's looking at the first january 2010 but unfortunately there is no sale in uh in sorry
40:59
it's looking at the 1st December 2010 because it's the previous month and there's no sale in 2010
41:05
in 1st December 2010 and so forth so on but if we go downwards on 29th of January 2011 it shows
41:15
14,000 sales because we have a sales in December 29th and 14,000 as well but previous month is
41:27
giving us the sales forever because previous month we the function as we look at the previous year
41:34
before it does not matter what date context or it's looking at it's always looking at this context
41:40
and giving us the full range so in this case it's giving me and i'm looking at the 1st january 2011
41:46
it is always giving me full of december 2011 and then all of a sudden this is repeating your numbers
41:53
then sometimes we get caught into this is why these numbers are repeating and
41:57
that is a reason because if we go back to our previous table where we were
42:05
looking at this thing the previous in this example it's a previous year but the
42:13
same concept it's always giving us the full range regardless of whether you're
42:19
looking at a date or a month and whatnot. So similarly in this case what is happening is
42:26
it giving us the full December 2011 sale not filtering for so depending on your use case
42:34
you will be you can use these functions. I think given the time I have only five more minutes
42:41
So this gives you a good idea how you can work with these time-intelium function
42:51
Again, it goes back to understanding what date range I'm getting at this particular row or this particular row
43:01
which you can debug the way I showed you with those measures
43:06
And then I can see you can. This case it is giving me 1st December 2011
43:11
country 10 to 31st December for previous month But for the data it is dynamic It changing depending on what what at what level I looking at the data What is my role context is a month range or it a date
43:29
There are no more examples, but I think given the time I will
43:32
quickly jump over to to my presentation here. So of course we're looking at the time
43:40
intelligence so best practices in I think somebody here again. Always always always always
43:48
add a date table in your model. You know it's no brainer
43:52
Even you're not going to use the date. I as I said, I created a template for myself
43:57
Every time I start a new report, I start with the template. It has a date dimension already in there
44:03
or whatever the columns I need. Those are already in there. I had turned off the date load
44:10
the data load, a time and the auto time, take time because there's no reason to have those hidden tables and and just increase the size of
44:19
our model sort columns always um you know in the date dimension you can have like a month like
44:27
january february march april may you you if we use those columns directly they're going to be
44:32
alphabetically april is going to be the first one and so august then and of course you can sort it
44:38
by month number. There's a sort by technique. Of course, it's nothing to do with a date, but
44:46
just commonly used to sort your one column by another column. I have a blog post which is
44:52
talking about the basic date dimension which you can take the DAX which I showed you and
44:57
you can use in your Power BI reports. Remove aggregation from all number fields. We have a
45:04
year column in date dimension, we have a day number, month number. We're never going to aggregate by
45:08
default, those are number fields and if you drop them, if you drop the year on a canvas, if it's
45:17
a number field and the aggregation is on, it's going to sum up all the years. You're never going
45:21
to sum up the year, so always change all the date and year number columns in your date dimension and
45:28
and change the aggregation to do not summarize. Always mark date as in date table
45:35
As I said, there is a special metadata. There is a link in my presentation
45:41
which we own for SQL BI, Marco Russo and Velocco Ferrari website
45:45
which talks about why it is important to mark table as a date table
45:50
Don't want to go in detail on that. As I just said, turn off auto date time
45:56
and keep one date table in the model as much as possible
46:00
I mean, sometimes it's not possible. You need more than one. There are different use cases for that
46:05
but the approach should be to keep one date table and have it connected to all your other tables
46:14
which has the date common date fields in there, or columns in there, and linked with that
46:20
If one table has multiple date columns, you can have inactive relationship
46:24
You can make those relationship active in your measures using the user relationship function
46:32
Again, there are links in this time Italian functions on the website at the table, how row based time
46:40
and there are a couple of other common scenarios where people say is I want a slicers where they say
46:46
previous year, previous month, same period last year or something like that
46:51
They would want to create as a slicer that person click on a slicer and then it gives you the date range so there's some techniques
46:59
i have shared some interesting links here which you can read and expand the tables in dax is
47:04
another thing which is important to learn uh this it is in one of my demo but unfortunately we
47:09
cannot cover it today so um i think that's uh that's pretty much again um uh this is i think
47:16
feedback from this uh from the organizers you can scan these codes to send the speaker feedback and
47:23
and even feedback. Thanks for attending my session. Again, my contact information is here
47:30
Please reach out to me anytime if you if you need an assistant or anything
47:36
in any question you have around this or anything related to power BI
47:41
Thank you
#Online Communities
#Time & Calendars