I'd be extreemly grateful if someone could help with this. I have a column containing date and time information. From this I need to create a new column which shows the age based on the date (in days). I also have a seperate dataset with a start date and end date (date and time) which I need to calculate the days between the start and end.

Anonymous Use "New Column" not "New Measure"

Calculating age from a date and time | Microsoft Fabric Community

12 Replies

RayL
Advocate I
7 years ago
FYI-

another quick way to convert DOB to age:
In Query Editor> select the column that contains DOB
Go to Date & Time Colmn section > Date> Select "Age"
In Date & Time Colmn section > Duration > Select "Total Years"
Highlight the transformed column, right click and select "Change Type"> Select "Whole Number"
Rename the column
- mplakin
  New Member
  7 years ago
  One issue with this method is that for anyone that is over 6 months into their "current" age, their age will be rounded up. E.g. 26.7 will be rounded up to 27.
  
  The solution to this is before you convert the column to a whole number, instead right click on the column header and go to Transform -> Round -> Round Down.
- sahibar
  Regular Visitor
  7 years ago
  I had similar requirement of calculating age from DOB.
  This is the easiest way to implement.
  
  Thank you!!
- AkshayManke
  Helper II
  2 years ago
  What a Fantastic Way RayL, Many thanks for Sharing.!
Vvelarde
Community Champion
9 years ago
Anonymous

hi,

To calculate the age in Days:

Age = DATEDIFF(Table1[DateTimeColumn],TODAY(),DAY)

to calculate the days between the start and end.

DaysBetween = DATEDIFF(Table1[StarDate],Table1[EndDate],DAY)
- Anonymous
  Not applicable
  9 years ago
  Thanks you...
  
  I seem to have done something wrong (assuming it's me as a newbie):
  - Vvelarde
    Community Champion
    9 years ago
    Anonymous
    
    Use "New Column" not "New Measure"
- Anonymous
  Not applicable
  6 years ago
  
  I used the Query Editor to "Insert a Custom Column" (Add Column tab). I inserted the formula given here, but PowerBI doesn't seem to like the DATEDIFF function. The system inserted the Table.AddColumn bit up to the DATEDIFF.
  = Table.AddColumn(#"Removed Columns", "ItemAge", each DATEDIFF([FirstDetected],TODAY(),DAY))
  Advice welcome.
- jdubs
  Helper V
  1 year ago
  Hello,
  
  If I then wanted to create a measure of the AVERAGE of all the "ages" how would I achieve this?