0:00
Hello everyone and welcome back. In this video we'll see the next level of M functions related to date and you'll find this very much interesting
0:08
Now till now what we have seen, we have seen whether the date is in the current period, previous or next period
0:13
But now we'll see whether the date belongs to previous N years, previous N months, previous N quarters and so on
0:21
And same way, we'll see how to find out whether the date is in next N years, N months and N quarters
0:27
Now this N can be replaced by your favorite value. It can be 3, 4, 5, 6, anything
0:33
Now, let us see an example how to work on that. So what I've done is I have created two sheets and I'll be showing you two examples here
0:40
The first one, if you see, I have the current date as 11th of September 2019 and now the date I have created this way
0:47
So that's an order date which is given. So purposely I have kept same dates
0:52
If you see similar dates I've kept, same month I have kept and the years are changing
0:56
So my question is I want to find out whether the date in this column belongs to previous three years or next three years
1:03
Okay, so that is previous N years or next N years. So now let us see how to get it
1:09
So 2019 is the current year. If I want to find out previous three years, so 2018, 2017 and 2016 should be the answer
1:17
Next should be 2020, 21, 22. How do I do that? So I'll just keep my cursor in the data, click on data tab and from table
1:25
So here you first need to change the data type of the column
1:29
So I'll just click here select the option date and replace current step Next one I just click on add column and I select the option as custom column See the function is very much similar So I just zoom it and I write here a date dot is in previous N years Okay So here I write as I just double click on order date comma 3 Now this is something new which I writing here a parameter So I write here as date is in previous N years This is the column name and that is N parameter It can be 3 4 5
2:04
5, 10, 12, anything, no problem. So now, this is the column name
2:08
Make sure you don't type it. You just double click and get it. After doing that, I'll just change the column name last three years
2:17
Okay, I'll just check whether it is last three years or not. And then you have to check whether the syntax are correct or not
2:22
I'll just click on okay. Now, if you observe here, I'm getting for 2016, 17, 18 as true
2:28
That means this date belonged to the previous three years. Now, same way I'll create for next three years
2:33
add column custom column and I'll just type here as next N years I'll type here as next three years
2:43
I'll write as date dot is in next N years okay and I'll just double click here comma three
2:55
and I'll just click on okay so once I click on okay I should get the answer here and if you see
3:00
For 2019 is the current year but 2020, 2021, 22. These three are the next three years. Okay, so I'll just click on home and I'll just click on close and load to
3:09
So that I can show you the data is changing here Existing worksheet click on this button here select this data Okay and now Load So I have got the date here Now what will do is I try to purposely change If you see so last three years this is showing us 2016 17 18 and next three years it is showing here properly
3:30
What I'll do is I'll purposely write here next year date. So today's 2019
3:35
I'll type as 2020. So I'll just click here and type 2020. Okay
3:40
Now, here the value should change and it should show me it is like in the next three years
3:44
Today is 2019, it should show 2020. So keep your cursing the data and click on refresh
3:49
Carefully observe, the value has changed to true and here the date has changed, right
3:53
So now I hope you can do the other options also same way
3:57
I've shown you one example for previous end years and next end years. One more example related to previous and next end months
4:03
Very much similar. Now what I've done is I have kept a scenario something different here
4:09
That's a 2019 calendar. And if you see everywhere it is first off and the month is changing 2019
4:14
Today's date is 11th of September. I want to find out previous three months and next three months
4:19
So this is September, I should get this three as true and the next three has true if I select previous and next
4:25
Let's do that. Keep your cursor in the data, data tab and from table
4:30
Now, again, I'll create, first I'll change the data type of the column. I'll select the option as date, replace current step
4:38
And now it's a good practice to keep on changing the data type and add column and I'll select the option as custom column
4:44
Now I'll zoom the screen so you can see the function. Type as date dot is in previous and months Open bracket and I just double click here comma three okay that a column name so I type here as previous three months okay and I just click on
5:08
okay button here so observe here carefully whenever there was a month of let's say
5:13
the other previous three months there I'm getting the answer as two I'll write one
5:17
more example here add custom column and I'll just zoom the screen so you can see the
5:21
data date dot is in you will become habitual, I'll say once I write, next and years, months
5:34
Once you keep on writing, you'll get a practice of writing these functions. You'll understand which one is capital and small case
5:40
I'll write us next three months. And then you can just click on OK button here
5:47
So once you click on OK, the next three months you're getting. So now I'll just click on home, I'll click on close and load to, and I'll try to paste the data
5:54
to that place existing worksheet and I'll select the option here okay load right so that's a data here so if you observe the this is the current
6:05
date September and the previous three months I'm getting true and the next three
6:09
months if you see I'm getting here okay any data changes here and you'll get
6:13
the answer here so what I'll do is I'll purposely copy the data of August control
6:17
C and I'll paste here control P okay and now I can just click on data refresh and if
6:22
you see the value will change to true part. So I think rest others you can do. I've given two examples from here to example from there
6:29
The other three you can just find out and try by yourself, right? I hope you have understood
6:34
how to work on M functions related to date and that's all for this video
