topic Re: Datadiff returns wrong value in DAX Commands and Tips

Datadiff returns wrong value

Anonymous — Thu, 27 Oct 2022 16:25:48 GMT

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

Re: Datadiff returns wrong value

daXtreme — Thu, 27 Oct 2022 16:54:08 GMT

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

Re: Datadiff returns wrong value

tamerj1 — Thu, 27 Oct 2022 17:04:34 GMT

Hi Anonymous

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

Re: Datadiff returns wrong value

Anonymous — Thu, 27 Oct 2022 17:23:07 GMT

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!

Re: Datadiff returns wrong value

tamerj1 — Thu, 27 Oct 2022 17:37:45 GMT

Anonymous

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.