cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RanjanThammaiah
Helper V
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.

 

Traveller.png

1 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





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-08-20 172529.png

For the related .pbix file,pls see attached.

 
 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 

 

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 NameTravelStartDateTravelEndDateDifferenceActual Difference
FARRELL/WILLIAM BERNARD17-Mar-1921-Mar-195.005.00
SHINTANI/TOSHIKAZU6-May-1910-May-195.005.00
OHIRA/YOICHI3-Jul-193-Jul-191.001.00
TADA/KENTARO3-Jul-199-Jul-197.006.00
KIDA/MORIAKI28-Jul-191-Aug-195.005.00
FARRELL/WILLIAM BERNARD2-Aug-1925-Aug-1924.0024.00
EBINA/KAZUHIRO3-Aug-199-Aug-197.00 
OHIRA/YOICHI3-Aug-199-Aug-197.00 
YAMADA/SANAE3-Aug-197-Aug-195.00 
KIKUI/TAKAMASA9-Aug-199-Aug-191.00 
MURAKAMI/RENA14-Oct-1919-Oct-196.006.00
MURAKAMI/RENA19-Oct-1919-Oct-191.00 
KIKUI/TAKAMASA11-Nov-1912-Nov-192.002.00
ARIGA/HANA22-Nov-1928-Nov-197.007.00
HIRATA/YOICHI25-Nov-1928-Nov-194.00 
SEWARD/JOHN25-Nov-1928-Nov-194.00 
    61.00 days

 

Thanks,

Ranjan

 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

Hi @amitchandak 

 

Here is the data in table format.

Kindly let me know, if this can be possible.

 

TravelStartDateTravelEndDateTraveller NameBU
3-Aug-199-Aug-19EBINA/KAZUHIROJP301
3-Jul-193-Jul-19OHIRA/YOICHIJP301
3-Aug-199-Aug-19OHIRA/YOICHIJP301
6-May-1910-May-19SHINTANI/TOSHIKAZUJP301
3-Jul-199-Jul-19TADA/KENTAROJP301
3-Aug-197-Aug-19YAMADA/SANAEJP301
22-Nov-1928-Nov-19ARIGA/HANAJP301
17-Mar-1921-Mar-19FARRELL/WILLIAM BERNARDJP301
2-Aug-1925-Aug-19FARRELL/WILLIAM BERNARDJP301
25-Nov-1928-Nov-19HIRATA/YOICHIJP301
28-Jul-191-Aug-19KIDA/MORIAKIJP301
9-Aug-199-Aug-19KIKUI/TAKAMASAJP301
11-Nov-1912-Nov-19KIKUI/TAKAMASAJP301
14-Oct-1919-Oct-19MURAKAMI/RENAJP301
19-Oct-1919-Oct-19MURAKAMI/RENAJP301
25-Nov-1928-Nov-19SEWARD/JOHNJP301

@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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors