cancel
Showing results for
Did you mean:
Highlighted
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

## 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!
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/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
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")

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)

Solution Sage

## Re: How do an average for a column type date

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

FOrrest

Helper IV

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

Helper IV

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????

Announcements

#### Announcing the New Spanish Forum

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

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘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