Tax has dozens upon dozens of different functions that are useful for very different purposes.
I'm not going to be able to cover the vast majority of these.
However, I can cover the handful that I consider most important in this lesson.
I'm going to start with a date function.
If you work with such date data, then you're probably familiar with all of the lunacy that goes into
figuring out how many months or years may have passed between two dates.
In Excel, for example, you might have written functions where you take the difference between the
years, multiply it by 12 and then add the difference between the months, all to get to number of months
that are in between two points.
Dative can be used for creating calculating columns or for creating measures.
We'll start this lesson by creating a calculated column in our data model.
Our data is an ideal for this, but we're going to do it anyways.
I'm going to use the purchase orders table since well, maybe.
Conceivably we would want to know how long it has been since the purchase was made.
We're would add a new column and set it equal to the date function.
This function will take two dates, the first date will be the starting date, and the second date will
be the end date, followed by the type of difference that you want between the two dates.
For this calculation, I want to know how many months it has been since each of these purchases.
That means that my starting date will be the date field for the current date.
I could manually plug in today's date or in this case, I'm going to use a helper function.
There's a handy today function that will return whatever today is as a date and it has a sister function
now that will return the current date and time together.
Either will work, but I'm going to use today out of personal preference the final input as the type
of difference that we want, whether it's days, months, years or something else.
For this input, the teletext gives us the list of available context.
I'm going to go ahead and type monthan, close the function and press enter.
Our new column will show us how many months have elapsed between the provided date and today.
I can change it to show the number of days as easily as changing the final input from month today.
Now, let's go see this in action within a measure, the difference between dates can be an important
computational piece, such as when computing compound interest or other time based information.
For this example, I'll create a measure that calculates the number of days within a given period of
time from our calendar table days and period.
And it will be fairly simple.
I'll use the date to function and take the difference between the minimum of our calendar date and the
maximum of our calendar date.
And then I'll select the day as my increment when I add it to the pivot table.
I notice that something is off the date difference isn't giving me a full year, and that's because
the difference between say twelve thirty, one twenty nineteen and one one twenty nineteen isn't three
hundred sixty five but only three sixty four.
So I'll add one to the total in my measure to correct for the overlapping day.
Now, when I extend months, I can see exactly how many days are in each month that I can still drill
down further to see that each day counts itself exactly one time.
That'll wrap up the date dysfunction, one of my favorite functions in both DACS and sequel.