Datadiff returns wrong value

[babyjb123]

Hi all,

I am calculating a simply date difference using Datediff function. But the value I got is wrong. I check the data types of the two date column are date. I still can not find the reason. Please help!

Thank you in advance!

Branko 

[tamerj1 · Accepted solution]

Hi @babyjb123 

if you change the aggregation from SUM to MAX or MIN you will get correct results at the table visual row level but wrong at the total level. 

You have two options. 
first option is to create a measure based on the Date Var column that you have created 

Difference (days) =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[Invoice Date], 'Table'[Service/ Tax Date] ),
    CALCULATE ( MAX ( 'Table'[Date Var] ) )
)

or directly using the following measure 

Difference (days) =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[Invoice Date], 'Table'[Service/ Tax Date] ),
    DATEDIFF ( 'Table'[Service/ Tax Date], 'Table'[Invoice Date], DAY )
)

[babyjb123]

Hi,

Thank you very much for your solution!!!!  But could you tell me the reason, why I can't get right outcoming by simply using DateDiff?

Thank you!

[tamerj1]

@babyjb123 

The problem is not with DATEDIFF. You are creating a calculated column and you have duplicate dates. It depends on your business logic. Actually, the first result you got is arguably correct. If you divide the number of days over how many times the 'Table'[Invoice Date] ; 'Table'[Service/ Tax Date] combination is dupicated in your data, you'll get the same result on my solution. However, it can be argued the first result is the correct one as it aggregates the days for each record. It is up to your business requirement and logic to determine which result is required. 

[daXtreme]

If in doubt... please consult DATEDIFF – DAX Guide