Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kb
Frequent Visitor

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?

EmployeeStart dateEnd date
A2/7/2011 
B2/14/20111/4/2016
C6/1/2011 
D6/1/20117/7/2014
1 ACCEPTED SOLUTION
kevhav
Continued Contributor
Continued Contributor

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)

View solution in original post

3 REPLIES 3
v-huizhn-msft
Employee
Employee

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
Not applicable

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.

 

2017-06-29_14-26_Sales Report.jpg

kevhav
Continued Contributor
Continued Contributor

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.