Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I want to return an average date of a column. It is possible ?
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")
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
Proud to give back to the community!
Thank You!
That looks right... , what's the Syntax Error? (Screen shot, or click Details wherever you see the error.)
Proud to give back to the community!
Thank You!
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????
Proud to give back to the community!
Thank You!
The returned values are false because i have a result = 2089/09/16
Can you add 'NumDays' to this example, i'm curious what your 5 digit dates look like...???
Proud to give back to the community!
Thank You!
DATE MOY = FORMAT(DATE(2000;1;INT(AVERAGE(TECHNIKE[NumDays])-36523));"YYYY-MM-DD")
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+
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
Hi @AlexGallet01,
What does the error message point to? Could you please translate it to English?
Regards,
Yuliana gu
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
@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.
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/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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |