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.

     

     

Bimster's avatar
Bimster
Frequent Visitor
8 years ago

Thanks for the help. I've tried this with the allexcept addition and the same error message is appearing.

 

It seems to be very complicated to do a date difference whilst ignoring blanks.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Dear Bimster,

 

Can you share your pbix (delete the sensitive data or create dummy data) please?

 

Regards,

ManNVSM.

  • Bimster's avatar
    Bimster
    Frequent Visitor
    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Dear Bimster,

       

      It's nice to help you. So choose a reply that you think it 's a solution, it'll help the other user :)

       

      Regards,

      ManNVSM.

      • amylehman's avatar
        amylehman
        New Member
        8 years ago

        I am experiencing this same issue, but this does not work for me. It removes the columns from the variance when the date is missing. 

         

        Date Variance = IF('Project'[ProjectBaseline1FinishDate]=BLANK(),'Project'[ProjectBaseline1FinishDate],1.0*'Project'[ProjectBaseline1FinishDate] -('Project'[ProjectBaseline0FinishDate]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello, Will this work in Direct Query?