Forum Discussion

Bimster's avatar
Bimster
Frequent Visitor
8 years ago
Solved

Date Difference with blank values

  Hi,   I'm trying to calculate the number of days between two dates and for one of these columns, there may be blanks which I want to ignore (rather than give a large -ve value).   What DAX do ...
Need Help
  • Bimster's avatar
    Bimster
    8 years ago

    Hi ManNVSM,

     

    Thanks for your help. You gave me the idea of using dummy data so I did this and came up with this formula:

     

    Days to close = if('Breach Register'[Breach closed date]=BLANK(),'Breach Register'[Breach closed date],1.0*'Breach Register'[Breach closed date]-'Breach Register'[Date that breach discovered])

     

    This seems to work ok.

     

    Thanks for your help again.

     

     

jthomson's avatar
jthomson
Solution Sage
8 years ago

Could do something like:

 

Days To Close = if(isblank([breach closed date]),blank(),[current measure you're using that's giving -43k something])

Bimster's avatar
Bimster
Frequent Visitor
8 years ago

The Days to close column is the one I have added as a calculated column to try and calculate the 'days to close' each breach. This used the following:

 

Days to close = 1.0*('Breach Register'[Date that breach discovered]-'Breach Register'[Breach closed date])

 

So I don't think your suggestion will work.

 

I did try datediff but it errored stating one date can;t be before another. This occurred whether it was x-y or y-x.

 

I am trying to add a column in the visual to show that where a breach has been closed (breach closed date), the time it took to close. If if hasn't been closed i.e. Breach closed date is blank, then the entry in the new column will also be blank.

 

Thanks.