Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

date difference calculation with positive and negative number

Hi All,   Currently i need to calculate 2 dates difference with Postive & negative numbers.   i need to have both positive and Negative numbers in calculation. when i use Datediff formula, i alwa...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    To create a calculated column as below.

     

    datediff = var diff = DATEDIFF('Table1'[Start date],'Table1'[Current date],DAY)
var diff2 = DATEDIFF('Table1'[Current date],Table1[Start date],DAY)
return
IF(Table1[Start date]<='Table1'[Current date],diff,0-diff2)

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous,

 

To create a calculated column as below.

 

datediff = var diff = DATEDIFF('Table1'[Start date],'Table1'[Current date],DAY)
var diff2 = DATEDIFF('Table1'[Current date],Table1[Start date],DAY)
return
IF(Table1[Start date]<='Table1'[Current date],diff,0-diff2)

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Matthew_Theis's avatar
Matthew_Theis
Advocate II
5 years ago

Hello,

 

Wondering if we need two variable declarations for this purpose.  Datediff provides the difference between the two dates defined - it doens't matter the order in the function.  The code below worked just fine for me.

 

Regards,

Matthew