Forum Discussion
Difference between two dates (DAX)
Hello DAX experts :)
I'am trying to figure out the formula that calculate an average time spent (in months) in company by previous and active employees (splitted by time periods - months/years). So I got 'start date' column and 'end date' column (for active emloyees remains empty). I did try DATEIFF function but every time it gaves me wrong numbers. Can somebody help me?
| Employee | Start date | End date |
| A | 2/7/2011 | |
| B | 2/14/2011 | 1/4/2016 |
| C | 6/1/2011 | |
| D | 6/1/2011 | 7/7/2014 |
Typically, I like to add an "Age" column in the query, rather than creating a calculated column with DAX, in the data model. For example, to do an "Age in Days" column...
- The query UI has a command for adding an "Age" column: select a date or date/time column; go to the "Add Column" tab; click Date; click Age.
- But it defaults to creating an Age since the current time. You can change the formula, e.g., like this:
=Table.AddColumn(#"Previous Step", "Age in Days", each ([End Date] - [Start Date]), type duration)
- But it defaults to creating an Age since the current time. You can change the formula, e.g., like this:
- However, this returns an age in days; and I don't see a proper conversion to months, using the "duration" data type. So it may not be ideal for your use case. Unless you are happy with estimating, e.g., by dividing by 30.
Or, if you do want to use a calculated column with a DAX formula, this works for me:
Age in Months = DATEDIFF([Start Date], [End Date], MONTH)
- The query UI has a command for adding an "Age" column: select a date or date/time column; go to the "Add Column" tab; click Date; click Age.
3 Replies
Typically, I like to add an "Age" column in the query, rather than creating a calculated column with DAX, in the data model. For example, to do an "Age in Days" column...
- The query UI has a command for adding an "Age" column: select a date or date/time column; go to the "Add Column" tab; click Date; click Age.
- But it defaults to creating an Age since the current time. You can change the formula, e.g., like this:
=Table.AddColumn(#"Previous Step", "Age in Days", each ([End Date] - [Start Date]), type duration)
- But it defaults to creating an Age since the current time. You can change the formula, e.g., like this:
- However, this returns an age in days; and I don't see a proper conversion to months, using the "duration" data type. So it may not be ideal for your use case. Unless you are happy with estimating, e.g., by dividing by 30.
Or, if you do want to use a calculated column with a DAX formula, this works for me:
Age in Months = DATEDIFF([Start Date], [End Date], MONTH)
- The query UI has a command for adding an "Age" column: select a date or date/time column; go to the "Add Column" tab; click Date; click Age.
Hi kb,
Have you resolved your issue? You'd better list your expected result according to the sample data. It's difficult to reproduce how to calculate the average time time according to your description.
Best Regards,
Angelia
