Forum Discussion
How do an average for a column type date
Hello,
I want to return an average date of a column. It is possible ?
20 Replies
AlexGallet01 when you say average for the date can you define what you mean exactly? A clear defintion will help steer you in the right direction.
I would like to show an average date of a date column
For exemple:
01/01/2015 02/01/2015 10/01/2015 AVERAGE DATE 03/01/2015 04/01/2015 05/01/2015 06/01/2015 07/01/2015 08/01/2015 09/01/2015 10/01/2015 11/01/2015 12/01/2015 13/01/2015 14/01/2015 15/01/2015 16/01/2015 17/01/2015 18/01/2015 19/01/2015 20/01/2015
When 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")
When i want to create custom column i have an error she says error syntax or i write this
NumDays = DATEDIFF(0,TECHNIKE[THCJDATE];DAY)
Is TECHNIKE[THCJDATE] formatted as a Date in Query Editor?
FOrrest
