Forum Discussion

xxenoss's avatar
xxenoss
Helper I
7 years ago
Solved

All possible date combinations

Hi, I'm trying to solve the trip duratiopn problem

My data looks like this. The first column is the date the train travels to certain destination and returns back the same day.

I need to find all possible trip durations from one point to another for up to 10 days for every date start point and destiantion.

For example:

If I will start my trip from Place_A to Destination_A in the first of May, I can stay at Destination_A for 1, or 3, or 4 days, etc.

If I will start my trip from Place_A to Destination_C in the first of May, I can stay at Destination_C for  2 days, as the next return train is scheduled for the 3rd of May

DateOriginDestination
1-MayPlace_ADestination_A
1-MayPlace_ADestination_C
1-MayPlace_ADestination_B
1-MayPlace_CDestination_B
2-MayPlace_JDestination_B
2-MayPlace_ADestination_A
2-MayPlace_ADestination_B
2-MayPlace_FDestination_B
3-MayPlace_IDestination_A
3-MayPlace_ADestination_D
3-MayPlace_ADestination_C
3-MayPlace_ADestination_B
3-MayPlace_CDestination_B
4-MayPlace_ADestination_A
4-MayPlace_ADestination_B
4-MayPlace_BDestination_A
4-MayPlace_EDestination_B
4-MayPlace_GDestination_B

 

  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi xxenoss

    Try this measure

    Measure =
SWITCH (
    TRUE (),
    [max duration] = 1, 1,
    [max duration] = 2, "1,2",
    [max duration] = 3, "1,2,3",
    [max duration] = 4, "1,2,3,4",
    [max duration] = 5, "1,2,3,4,5",
    [max duration] = 6, "1,2,3,4,5,6",
    [max duration] = 7, "1,2,3,4,5,6,7",
    [max duration] = 8, "1,2,3,4,5,6,7,8",
    [max duration] = 9, "1,2,3,4,5,6,7,8,9",
    [max duration] = 10, "1,2,3,4,5,6,7,8,9,10"
)

     

     

    Best Regards

    Maggie

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi xxenoss

    trip durations for every date start point and destiantion is detemined by the next return train's schedule, right?

    But I can't find the information about the next return train's schedule for each destiantion to start point from your example.

     

    Best regards

    Maggie 

    • xxenoss's avatar
      xxenoss
      Helper I
      7 years ago

      Hi Maggie,

       

      The return date, as I mentioned, is the same with the departure date, for example train starts 1st of May From Place_A to Destination_A in 10:00 in the morning and returns from Destination _A to Place_A at 6:00 in the evening of the same day.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi xxenoss

    Create measures

    min date =
CALCULATE (
    MIN ( [Date] ),
    FILTER (
        ALL ( Table1 ),
        [Origin] = SELECTEDVALUE ( Table1[Origin] )
            && [Destination] = SELECTEDVALUE ( Table1[Destination] )
    )
)


max date =
CALCULATE (
    MAX([Date])
    FILTER (
        ALL ( Table1 ),
        [Origin] = SELECTEDVALUE ( Table1[Origin] )
            && [Destination] = SELECTEDVALUE ( Table1[Destination] )
    )
)

duration = DATEDIFF([min date],[max date],DAY)+1

    the "duration" is the max days a customer can stays.

     

    Best Regards

    Maggie

    • xxenoss's avatar
      xxenoss
      Helper I
      7 years ago

      Hi Maggie, 

       

      thank you very much for the answer, it was very helpful. But the thing is that I need to store all possible duration from 1 day up to 10. Not sure if it they can be stored in one cell, separated by coma, or in different cells in the sae row.

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi xxenoss

        Try this measure

        Measure =
SWITCH (
    TRUE (),
    [max duration] = 1, 1,
    [max duration] = 2, "1,2",
    [max duration] = 3, "1,2,3",
    [max duration] = 4, "1,2,3,4",
    [max duration] = 5, "1,2,3,4,5",
    [max duration] = 6, "1,2,3,4,5,6",
    [max duration] = 7, "1,2,3,4,5,6,7",
    [max duration] = 8, "1,2,3,4,5,6,7,8",
    [max duration] = 9, "1,2,3,4,5,6,7,8,9",
    [max duration] = 10, "1,2,3,4,5,6,7,8,9,10"
)

         

         

        Best Regards

        Maggie