How to: Standardise dates to first of month and use this to calculate tenure

Many datasets have lists of dates but often you will want to standardise or round these to complete months.
If we want to calculate how long each employee has been in service at the start of the timescale we can round these so that we can calculate tenure in full months.
This is also useful for calculations like asset life depreciation, customer loyalty etc.
In the table with the dates, create a measure called Standardised Start.
We can use the Day function to get the day number from the date.
If we subtract this number from the start date and then add 1 it gives us the first of the month and year of the start date

To get the number of completed months at the timescale start we use the DATEDIF function with the ‘M’ parameter to indicate months

Now we can use the PERIOD function to give us the period number of each month within the timescale

If you wanted to count completed months use the DATEDIF function against the original date and the timescale start.
You could combine the result with a conditional to remove negative length of service values.