Days between two days

[sixtoquiles]

HI, I have a slicer date, i create two measure column to calculate the max and min of the dates in the slicer.

Example: The slicer had: 2/1/2017 to 2/11/2017.

MinDate = MIN(Query1[EffectiveDate]) 

2/1/2017

MaxDate = MAX(Query1[EffectiveDate])

2/11/2017

I want make another measure column to calculate how many days are in between.

NumDayBetween = DATEDIFF([MinDate],[MaxDate],DAY)

Result: 8

And if a chosse 2/1/2017 to 2/1/2017. NumDayBetween = 0

Why?

[Phil_Seamark · Accepted solution]

Hi @sixtoquiles

You say that "And if a chosse 2/1/2017 to 2/1/2017. NumDayBetween = 0"

To me they are the same day and so therefore the correct answer should be 0

I note in your image, you have different dates selected and that the MAX measure doesn't match the slicer selection.

I would recommend creating this on a DATE table where you are guaranteed to have 1 row per day with no gaps.  If your data table (in this case Query1) has no data on some days, you might see some odd things like this.