Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Datesbetween if date exists

Hi,

 

I'm trying to calculate dates between and everything but one seems to work. Measure fails (showing some huge number instead) when end date does not exist.

E.g.,

[EveryDate] - date range from 2017 until 2021

[Submit Date] - start date 

[LastCompletedDate] - last date (not entered in model for some rows)

[CompletedDiff] - date difference (count)

 

Submit Date | LastCompletedDate | Status | CompletedDiff

 2018-09-04                                      Closed        602

 

CompletedDiff = CALCULATE(SUM(MasterCalendar[IsWorkDay]),DATESBETWEEN('MasterCalendar'[EveryDate],'Model'[Submit Date],'Model'[LastCompletedDate]))

shows 602 days instead of 0 or 1. And this is pretty logical, but how to modify DAX so it would count empty dates as zerro? It would be good to have it like:

Submit Date | LastCompletedDate | Status | CompletedDiff

 2018-09-04                                      Closed           1 (or 0)

 

Thanks in advance.

  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Anonymous,

    If you want to show "0" when the value is blank, you just nedd to add "0" in your measure or calculated column:

     

    Regards,

    Daniel He

  • Anonymous's avatar
    Anonymous
    7 years ago

    UPD2
    CompletedDiff = CALCULATE(SUM(MasterCalendar[IsWorkDay]),DATESBETWEEN('MasterCalendar'[EveryDate],'Model'[PBM.Submit Date],'Model'[PBM.LastCompletedDate]+0))
    this seems is almost working:

     

     

6 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

    From your description, I could not understand if the [last date] is blank, the [Completed Diff] should be blank too.

    Could you please offer me more information about your data model and post your desired result if possible?

     

    Regards,

    Daniel He

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-danhe-msft,

       

      yes, it would be good to have it blank either 0. Even "1" is acceptible :) 

      Just thinking if there is a difference having it blank or zerro? I mean, in case I would use DateDiff data as to show average, wouldn't BLANK cause an issue counting values together? If it would, than 0 is more acceptible. 

       

      So in case Last Date is empty, show DateDiff as 0.

      Thank for your assistance so far!

      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous,

        If you want to show "0" when the value is blank, you just nedd to add "0" in your measure or calculated column:

         

        Regards,

        Daniel He