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. Please help!

 

Thank you in advance!

Branko 

 

 

 

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

     

4 Replies

  • 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.