DiffDate : Not a valid expression

[Nicolas]

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

[Anonymous]

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:

DATEDIFF Function (DAX)

MAXX Function (DAX)

MINX Function (DAX)

Regards,
Xiaoxin Sheng

[Nicolas]

Hi @Anonymous!

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!

[Anonymous · Accepted 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

[BhaveshPatel]

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