Calculate the difference between two dates (days/months)
Calculating the difference between two dates is a common requirement and one that you will likely need to do at some point. Below you will find the best practice for calculating the difference between two days, and the best practice for calculating the difference between two months.
While there are multiple ways to get to the answer you're looking for, often the simplest solution is the best. When calculating the difference between two dates (days or months), use this approach first:
For date formatted line items, use: line item 1 – line item 2.
For time formatted line items, use: START(line item 1) – START(line item 2).
Dates
1. Simple dates difference
2. Dates addition
3. Dates subtraction
Time periods
1. Time period difference
2. Months addition
3. Months subtraction
4. Years addition
5. Years subtraction
Keep 'superset' in mind when selecting your time period
If you choose a time period that is outside of the superset, it will display as blank. The superset is the earliest date from the model calendar and any time range through to the later of the last date of the calendar of any time range.
Example model:
Time:
Superset = Jan 17 - Dec 22:
If you go outside of that range:
You're now ready to calculate date differences in your daily practice. If you have additional questions or feedback, please add them to the comments below.
Comments
-
Helpful post as always, David. One use case not covered here is calculating the difference in months between 2 monthly periods. I have generally solved this with a formula:
15 -
Thank you David! Sometimes I forget how easy is to use substract and add with time.
Sometimes we also need to go outside of the defined time period and include those time ranges in our calculation.
In this case, what would you recommend to do?
3 -
Great article, @DavidSmith - simple and effective.
Also, does the kudos button for the article appear in a strange place for anyone else?
0 -
@CallumW - you mean under the "labels" box? Weird!
@emilydunn - worth having a look at why "kudos" doesn't appear directly under the article but off to the side or is it meant to be like that?
0 -
@andrewtye- precisely!
I've noticed kudos buttons dotted around in weird areas sometimes, but this one looked particularly odd!
0 -
@andrewtye You are correct about kudos. In a future update we'll be resolving this so that it is in the same spot for both Blogs and Knowledge Base articles.
1 -
Great post David. One workaround I've used to find the difference in months between 2 time periods (to @Mitch_Max point) is creating a sequential order against the time dimension using the formula below, and then just leveraging that in calculations through out the model.
Then once that is in place you can look up the difference between the periods using a simple lookup. This can be replicated at the week, month, quarter, year levels as well.
1
