Forum Discussion

OhLookAnError's avatar
OhLookAnError
Icon for Helper I rankHelper I
3 years ago
Solved

How to return a zero when subtrahend is blank

I am trying to acurately display days since opened and days since closed in calculated columns. My days since closed column which is:  

 

Days Since Closed = tblAccounts[Todays Date] - tblAccounts[Account Closed]

 

is returning this 48888 when there is no closed date. 

 

How can I get this to return zero if the Account closed Column is blank? 

 

Thanks in advance! 

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi OhLookAnError 
    Please try

    Days Since Closed =
tblAccounts[Todays Date]
    - COALESCE ( tblAccounts[Account Closed], tblAccounts[Todays Date] )

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi OhLookAnError 
    Please try

    Days Since Closed =
tblAccounts[Todays Date]
    - COALESCE ( tblAccounts[Account Closed], tblAccounts[Todays Date] )
  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    try this:

     

    Days Since Closed = 
    VAR _duration =
    tblAccounts[Todays Date] - tblAccounts[Account Closed]
    RETURN
    IF(
        [Account closed Column]=BLANK(),
        0,
       _duration 
    )