Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Datadiff returns wrong value

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. P...
Help
  • tamerj1's avatar
    tamerj1
    3 years ago

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

     

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      tamerj1
      Community Champion
      3 years ago

      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.