cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AlexGallet01 Helper IV
Helper IV

How do an average for a column type date

Hello,

 

I want to return an average date of a column. It is possible ?

19 REPLIES 19
Super User I
Super User I

Re: How do an average for a column type date

@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.


Did I answer your question? Mark my post as a solution If this solution works to help others. Kudos are nice too.
Proud a to be a Datanaut!
AlexGallet01 Helper IV
Helper IV

Re: How do an average for a column type date

I would like to show an average date of a date column

 

For exemple:

 

01/01/2015   
02/01/2015 10/01/2015AVERAGE 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   
fhill Solution Sage
Solution Sage

Re: How do an average for a column type date

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

 

 

Capture.PNG

 

AlexGallet01 Helper IV
Helper IV

Re: How do an average for a column type date

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)

fhill Solution Sage
Solution Sage

Re: How do an average for a column type date

Is TECHNIKE[THCJDATE] formatted as a Date in Query Editor?

FOrrest

AlexGallet01 Helper IV
Helper IV

Re: How do an average for a column type date

Sans titre.png

fhill Solution Sage
Solution Sage

Re: How do an average for a column type date

That looks right... , what's the Syntax Error?  (Screen shot, or click Details wherever you see the error.)

AlexGallet01 Helper IV
Helper IV

Re: How do an average for a column type date

Sans titre.png

fhill Solution Sage
Solution Sage

Re: How do an average for a column type date

I would throw some dates into an excel, and try to duplicate the error outside your current data set.  If it works in the 'basic version' then something isn't happy in your data set...  It's strange the error is on the Date Column name????

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors