1
00:00:00,960 --> 00:00:05,920
Tax has dozens upon dozens of different functions that are useful for very different purposes.

2
00:00:06,360 --> 00:00:09,240
I'm not going to be able to cover the vast majority of these.

3
00:00:09,390 --> 00:00:14,210
However, I can cover the handful that I consider most important in this lesson.

4
00:00:14,220 --> 00:00:16,200
I'm going to start with a date function.

5
00:00:16,770 --> 00:00:21,660
If you work with such date data, then you're probably familiar with all of the lunacy that goes into

6
00:00:21,660 --> 00:00:25,080
figuring out how many months or years may have passed between two dates.

7
00:00:25,500 --> 00:00:29,730
In Excel, for example, you might have written functions where you take the difference between the

8
00:00:29,730 --> 00:00:34,950
years, multiply it by 12 and then add the difference between the months, all to get to number of months

9
00:00:34,950 --> 00:00:36,480
that are in between two points.

10
00:00:37,170 --> 00:00:41,400
Dative can be used for creating calculating columns or for creating measures.

11
00:00:41,970 --> 00:00:45,840
We'll start this lesson by creating a calculated column in our data model.

12
00:00:46,230 --> 00:00:49,100
Our data is an ideal for this, but we're going to do it anyways.

13
00:00:49,440 --> 00:00:52,560
I'm going to use the purchase orders table since well, maybe.

14
00:00:52,560 --> 00:00:56,640
Conceivably we would want to know how long it has been since the purchase was made.

15
00:00:57,210 --> 00:01:01,080
We're would add a new column and set it equal to the date function.

16
00:01:02,000 --> 00:01:06,650
This function will take two dates, the first date will be the starting date, and the second date will

17
00:01:06,650 --> 00:01:10,850
be the end date, followed by the type of difference that you want between the two dates.

18
00:01:11,690 --> 00:01:15,770
For this calculation, I want to know how many months it has been since each of these purchases.

19
00:01:16,220 --> 00:01:20,200
That means that my starting date will be the date field for the current date.

20
00:01:20,240 --> 00:01:24,770
I could manually plug in today's date or in this case, I'm going to use a helper function.

21
00:01:25,230 --> 00:01:30,590
There's a handy today function that will return whatever today is as a date and it has a sister function

22
00:01:30,590 --> 00:01:34,370
now that will return the current date and time together.

23
00:01:34,760 --> 00:01:39,800
Either will work, but I'm going to use today out of personal preference the final input as the type

24
00:01:39,800 --> 00:01:44,240
of difference that we want, whether it's days, months, years or something else.

25
00:01:44,540 --> 00:01:48,470
For this input, the teletext gives us the list of available context.

26
00:01:48,650 --> 00:01:52,130
I'm going to go ahead and type monthan, close the function and press enter.

27
00:01:52,940 --> 00:01:57,650
Our new column will show us how many months have elapsed between the provided date and today.

28
00:01:58,190 --> 00:02:03,890
I can change it to show the number of days as easily as changing the final input from month today.

29
00:02:04,960 --> 00:02:10,000
Now, let's go see this in action within a measure, the difference between dates can be an important

30
00:02:10,000 --> 00:02:15,220
computational piece, such as when computing compound interest or other time based information.

31
00:02:15,940 --> 00:02:20,650
For this example, I'll create a measure that calculates the number of days within a given period of

32
00:02:20,650 --> 00:02:24,220
time from our calendar table days and period.

33
00:02:24,220 --> 00:02:25,560
And it will be fairly simple.

34
00:02:25,930 --> 00:02:31,090
I'll use the date to function and take the difference between the minimum of our calendar date and the

35
00:02:31,090 --> 00:02:32,740
maximum of our calendar date.

36
00:02:33,130 --> 00:02:38,240
And then I'll select the day as my increment when I add it to the pivot table.

37
00:02:38,500 --> 00:02:43,510
I notice that something is off the date difference isn't giving me a full year, and that's because

38
00:02:43,510 --> 00:02:49,240
the difference between say twelve thirty, one twenty nineteen and one one twenty nineteen isn't three

39
00:02:49,240 --> 00:02:51,640
hundred sixty five but only three sixty four.

40
00:02:52,960 --> 00:02:57,340
So I'll add one to the total in my measure to correct for the overlapping day.

41
00:02:58,370 --> 00:03:05,000
Now, when I extend months, I can see exactly how many days are in each month that I can still drill

42
00:03:05,000 --> 00:03:08,870
down further to see that each day counts itself exactly one time.

43
00:03:09,830 --> 00:03:15,020
That'll wrap up the date dysfunction, one of my favorite functions in both DACS and sequel.