Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

20 REPLIES 20
fhill
Resident Rockstar
Resident Rockstar

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

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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)

Hi,
a few years later from origin post (still not solved?) but try using , instead of ; in you formula

that could do the trick for the rest of the calculations

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

FOrrest




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Sans titre.png

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Sans titre.png

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




The returned values are false because i have a result = 2089/09/16 

 Sans titre.png

Can you add 'NumDays' to this example, i'm curious what your 5 digit dates look like...???




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




DATE MOY = FORMAT(DATE(2000;1;INT(AVERAGE(TECHNIKE[NumDays])-36523));"YYYY-MM-DD")

Sans titre.png

Sans titre.png

 

NumDays = DATEDIFF(0;IF(TECHNIKE[THCJDATE]>0;TECHNIKE[THCJDATE];0);DAY)

Here is the issue...  These values are wrong...  I'm not sure how your 'Date MOY' is being caculated (or where it's from) but 2015-12-08 and 2015-12-06 should only be 2 NUMDays apart, not 230,000+




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




I try to modify the colum but i have an error and i don't understand why i have this error

Sans titre.png

Hi @AlexGallet01,

 

What does the error message point to? Could you please translate it to English?

 

Regards,
Yuliana gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

My start date are above my end date

 

Or it's impossible because my start date are 0

Hi @AlexGallet01,

 

To use DateDiff function, we should meet the syntax that "the start date cannot be greater than end date". Otherwise, it will prompt error.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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   

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.