Forum Discussion
AlexGallet01
Helper IV
9 years agoHow do an average for a column type date
Hello, I want to return an average date of a column. It is possible ?
fhill
Resident Rockstar
9 years agoWhen it comes to dates, there are two versions of the term 'average'...
1. If you want the 'MEDIAN' date, or the date in the middle of your date range, you can use this formula: (See first Date 2 column)
Add_Date2 = FIRSTDATE(Table1[Date2]) + INT(DATEDIFF(FIRSTDATE(Table1[Date2]),LASTDATE(Table1[Date2]),DAY) / 2)
2. If you truly want the Average or Mean date (taking each date into a weighted value) you can use this formula. But see how the value is different in the 2nd Date 2 / Num Days column.
Custom Column ... NumDays = DATEDIFF(0,Table1[Date2],DAY)
Measure Mean = FORMAT(DATE(2000,1, INT(AVERAGE( Table1[NumDays] ) -36523)),"YYYY-MM-DD")