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.
Hi everyone
I'm trying to create a new column that takes two Date columns from two different tables and calculates the difference.
So far, i've been trying this:
MyNewTable = DATEDIFF(FirstTable[BeginningDate], LastTable[FinishDate])
and
MyNewTable = DATEDIFF(FIRSTDATE(FirstTable[BeginningDate]), LASTDATE(LastTable[FinishDate]))
but I get an "DATEDIFF Not Recognized"
As well, how could i unclude only week days in this query?
Thanks for your help
Solved! Go to Solution.
Hi @Nicolas,
I modify the formula, and it works on myside, you can refer to below formula:
Diff Table = ROW("diff DAY",
DATEDIFF(
MINX(CALCULATETABLE('First Table',AND(WEEKDAY('First Table'[Date],1)<>1,WEEKDAY('First Table'[Date],1)<>7)),[Date]),
MAXX(CALCULATETABLE('Last Table',AND(WEEKDAY('Last Table'[Date],1)<>1,WEEKDAY('Last Table'[Date],1)<>7)),[Date]),DAY)/COUNT('First Table'[Date])
)
In addition, since the formula return single value, I think use a measure to store it more suitable.
Measure=
DATEDIFF(
MINX(CALCULATETABLE(All('First Table'),AND(WEEKDAY('First Table'[Date],1)<>1,WEEKDAY('First Table'[Date],1)<>7)),[Date]),
MAXX(CALCULATETABLE(All('Last Table'),AND(WEEKDAY('Last Table'[Date],1)<>1,WEEKDAY('Last Table'[Date],1)<>7)),[Date]),DAY)/COUNT('First Table'[Date])
Regards,
Xiaoxin Sheng
Hi @Nicolas,
You can use below formula to get the diff between two tables(I already removed the weekdays):
Diff Table = ROW("diff SECOND", DATEDIFF( MINX(CALCULATETABLE('First Table',AND(WEEKDAY('First Table'[Date],1)<>1,WEEKDAY('First Table'[Date],1)<>7)),[Date]), MAXX(CALCULATETABLE('Last Tabel',AND(WEEKDAY('Last Tabel'[Date],1)<>1,WEEKDAY('Last Tabel'[Date],1)<>7)),[Date]),SECOND) )
Reference:
Regards,
Xiaoxin Sheng
Hi @v-shex-msft!
Thanks a lot, that is working! Last little question, I'm trying to divide this number by the COUNT of 'First Table'.
I tried this but it is not working:
Diff Table = ROW("diff DAY", DATEDIFF( MINX(CALCULATETABLE('First Table',AND(WEEKDAY('First Table'[Date],1)<>1,WEEKDAY('First Table'[Date],1)<>7)),[Date]), MAXX(CALCULATETABLE('Last Tabel',AND(WEEKDAY('Last Tabel'[Date],1)<>1,WEEKDAY('Last Tabel'[Date],1)<>7)),[Date]),DAY) )/COUNT('First Table'[Date])
Should I add a CALCULATETABLE to COUNT?
Thanks!
Hi @Nicolas,
I modify the formula, and it works on myside, you can refer to below formula:
Diff Table = ROW("diff DAY",
DATEDIFF(
MINX(CALCULATETABLE('First Table',AND(WEEKDAY('First Table'[Date],1)<>1,WEEKDAY('First Table'[Date],1)<>7)),[Date]),
MAXX(CALCULATETABLE('Last Table',AND(WEEKDAY('Last Table'[Date],1)<>1,WEEKDAY('Last Table'[Date],1)<>7)),[Date]),DAY)/COUNT('First Table'[Date])
)
In addition, since the formula return single value, I think use a measure to store it more suitable.
Measure=
DATEDIFF(
MINX(CALCULATETABLE(All('First Table'),AND(WEEKDAY('First Table'[Date],1)<>1,WEEKDAY('First Table'[Date],1)<>7)),[Date]),
MAXX(CALCULATETABLE(All('Last Table'),AND(WEEKDAY('Last Table'[Date],1)<>1,WEEKDAY('Last Table'[Date],1)<>7)),[Date]),DAY)/COUNT('First Table'[Date])
Regards,
Xiaoxin Sheng
Hi There,
You should add 3rd argument as DAY/MONTH/YEAR in your DATEDIFF formula.
With your second question,
Please refer to following blog post for demo of creating weekdays calculation.
Thanks & Regards,
Bhavesh
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 |
---|---|
113 | |
98 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |