Date difference with traveler start and end date by Excl Duplicate dates

[RanjanThammaiah]

Hi All,

Could someone suggest me getting this working?

 I wanted to calculate the total number of days from travel start and end date by excluding the duplicate dates in between.

In the below Eg 3/7/19 should not be considered for TADA/KENTARO because it was already counted for OHIRA/YOICHI.

Basically for TADA/KENTARO, I should be getting 6 days(excluding 3rd, which was already considered for OHIRA/YOICHI).

Let me know if this could be possible.

[v-kelly-msft]

Hi  @RanjanThammaiah ,

First create an index column;

Then create a column as below:

_diff = 
var _diff1=CALCULATE(COUNT('Table'[TravelStartDate]),FILTER('Table','Table'[TravelStartDate]=EARLIER('Table'[TravelStartDate])&&'Table'[BU]=EARLIER('Table'[BU])&&'Table'[Index]<EARLIER('Table'[Index])))
Return
'Table'[Unique date diff]-_diff1

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[RanjanThammaiah]

Thanks, @v-kelly-msft & @amitchandak 

The logic should be if A & B Travelling to X To Y place.

"A" Travel from 1st August to 15th August and "B" travels from 5th August to 20th August.

So basically I need to be considering for "A" 15 days(1st to 15th) and "B" 5 days (15th to 20th). total should be 20 days.

below should be the answer to my query in excel. it should be 61 days., The Blank row dates are already considered in the previous row dates.

Traveler Name	TravelStartDate	TravelEndDate	Difference	Actual Difference
FARRELL/WILLIAM BERNARD	17-Mar-19	21-Mar-19	5.00	5.00
SHINTANI/TOSHIKAZU	6-May-19	10-May-19	5.00	5.00
OHIRA/YOICHI	3-Jul-19	3-Jul-19	1.00	1.00
TADA/KENTARO	3-Jul-19	9-Jul-19	7.00	6.00
KIDA/MORIAKI	28-Jul-19	1-Aug-19	5.00	5.00
FARRELL/WILLIAM BERNARD	2-Aug-19	25-Aug-19	24.00	24.00
EBINA/KAZUHIRO	3-Aug-19	9-Aug-19	7.00
OHIRA/YOICHI	3-Aug-19	9-Aug-19	7.00
YAMADA/SANAE	3-Aug-19	7-Aug-19	5.00
KIKUI/TAKAMASA	9-Aug-19	9-Aug-19	1.00
MURAKAMI/RENA	14-Oct-19	19-Oct-19	6.00	6.00
MURAKAMI/RENA	19-Oct-19	19-Oct-19	1.00
KIKUI/TAKAMASA	11-Nov-19	12-Nov-19	2.00	2.00
ARIGA/HANA	22-Nov-19	28-Nov-19	7.00	7.00
HIRATA/YOICHI	25-Nov-19	28-Nov-19	4.00
SEWARD/JOHN	25-Nov-19	28-Nov-19	4.00
				61.00 days

Thanks,

Ranjan

[mahoneypat · Accepted solution]

Ok.  This one was tricky/fun.  Please put this expression in a calculated column.  You can then sum it as needed (it totals to 61 with your example data).  You can make a measure version of it, but I spent too long on it already.  Plus, there is a lot of calculation going on, so depending on how many rows you have, you may want it precalculated in a column for performance reasons.

Unique Travel Days =
VAR thisstart = Travel[TravelStartDate]
VAR thisend = Travel[TravelEndDate]
VAR thistable =
    CALENDAR ( thisstart, thisend )
VAR days =
    COUNTROWS ( thistable )
VAR total =
    COUNTROWS (
        FILTER (
            thistable,
            VAR thisdate = [Date]
            RETURN
                NOT (
                    ISBLANK (
                        COUNTROWS (
                            FILTER (
                                Travel,
                                Travel[TravelStartDate] <= thisdate
                                    && Travel[TravelEndDate] >= thisdate
                                    && Travel[TravelStartDate] <= thisstart
                                    && Travel[Traveler Name] <> EARLIER ( Travel[Traveler Name] )
                            )
                        )
                    )
                )
        )
    )
RETURN
    days - total

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@RanjanThammaiah , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[RanjanThammaiah]

Hi @amitchandak 

Here is the data in table format.

Kindly let me know, if this can be possible.

TravelStartDate	TravelEndDate	Traveller Name	BU
3-Aug-19	9-Aug-19	EBINA/KAZUHIRO	JP301
3-Jul-19	3-Jul-19	OHIRA/YOICHI	JP301
3-Aug-19	9-Aug-19	OHIRA/YOICHI	JP301
6-May-19	10-May-19	SHINTANI/TOSHIKAZU	JP301
3-Jul-19	9-Jul-19	TADA/KENTARO	JP301
3-Aug-19	7-Aug-19	YAMADA/SANAE	JP301
22-Nov-19	28-Nov-19	ARIGA/HANA	JP301
17-Mar-19	21-Mar-19	FARRELL/WILLIAM BERNARD	JP301
2-Aug-19	25-Aug-19	FARRELL/WILLIAM BERNARD	JP301
25-Nov-19	28-Nov-19	HIRATA/YOICHI	JP301
28-Jul-19	1-Aug-19	KIDA/MORIAKI	JP301
9-Aug-19	9-Aug-19	KIKUI/TAKAMASA	JP301
11-Nov-19	12-Nov-19	KIKUI/TAKAMASA	JP301
14-Oct-19	19-Oct-19	MURAKAMI/RENA	JP301
19-Oct-19	19-Oct-19	MURAKAMI/RENA	JP301
25-Nov-19	28-Nov-19	SEWARD/JOHN	JP301

[amitchandak]

@RanjanThammaiah , Check the file attached after signature and let me know you want to subtract a day whenever there is value in [Reduce day] column or some other logic is required