Forum Discussion

Samoe's avatar
Samoe
Advocate I
2 years ago

DATEDIFF function that counts first day

Hello all!

I am building a report and am calculating lead times. My data is structured pretty much like this:

Now, I already had achieved thanks to some help here to create a measure to calucate the days from start to release, for info 

Measure = DATEDIFF(max(Table[Start date]), max(Table[release date]), DAY).

But now I have a problem:

1. In fact, I want to count all the days from start to release because the start day counts already! (so in the first row, this would return 4). However, the datediff function does not count the first day (thus returning 3 in this example).

--> How can I fix this? 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Samoe 

    Measure = DATEDIFF(max(Table[Start date]), max(Table[release date]), DAY) + 1

     

    ?

    • Samoe's avatar
      Samoe
      Advocate I
      2 years ago

      haha, I thought of that too. But then I felt like "this cannot be a good solution". It feels not correct to me to do it like that, even though ofc. the result will be correct ğŸ˜…

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        Samoe Lol! Well, you could use COUNTROWS combined with CALENDAR like this:

        COUNTROWS(CALENDAR(MAX('Table'[Start date]), MAX('Table'[Release date])))

        However, that's probably going to perform significantly less fast. In fact, if you want your function to be as fast as possible, probably should do this:

        Measure =
  VAR __Start = MAX('Table'[Start date])
  VAR __End = MAX('Table'[Release date])
  VAR __Result = __End - __Start + 1
RETUR
  __Result