Difference between two dates (DAX)

[kb]

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

[v-huizhn-msft]

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

[Anonymous]

Hi there,

Here is the image how to do it:

1. Open 'Edit Queries'
2. Select the date column
3. Click on the Date > Age in the tab
4. It returned me minutes by default, but it is easy to select the needed measure. Click on the 'Duration' in the tab.

[kevhav · Accepted solution]

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)
• 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)