cancel
Showing results for
Did you mean:
Helper V

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

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.

1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

6 REPLIES 6
Community Support

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

Helper V

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

Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Super User

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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Helper V

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
Super User

@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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.