Forum Discussion
Solved
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)....
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.
