Number of days between Earliest & Latest Dates

[android1]

Hi,

I have a date column which I've added to a table. I want to see the earliest & latest dates do I've added this column twice.

How do I find the number of days between the earliest & latest dates. See pic.

Thanks,

android1

[gooranga1 · Accepted solution]

hi @android1

I would create 2 measures to calculate the Start and End Date

Maximum Date = calculate(max([A/ Leave Date]))

Minimum Date = calculate(min([A/ Leave Date]))

Then a 3rd measure

difference = datediff([Minimum Date],[Maximum Date],DAY)

if your table has multiple rows for employee absence/attendance this method will only take the first and last date for that and calculate the days in between,

[gooranga1 · Accepted solution]

hi @android1

I would create 2 measures to calculate the Start and End Date

Maximum Date = calculate(max([A/ Leave Date]))

Minimum Date = calculate(min([A/ Leave Date]))

Then a 3rd measure

difference = datediff([Minimum Date],[Maximum Date],DAY)

if your table has multiple rows for employee absence/attendance this method will only take the first and last date for that and calculate the days in between,

[android1]

Yeah, that works real good. Thanks.

One problem tho. If my min date is the same as my max date I want the difference to be 1 day.

So if min is 29/01/18 & max is 29/01/18 then difference needs to be 1 day.

[Greg_Deckler · Accepted solution]

difference = IF([Minimum Date] = [Maximum Date],1,datediff([Minimum Date],[Maximum Date],DAY))

[android1]

Thats brilliant. Thanks very much.