All possible date combinations

[xxenoss]

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

Date	Origin	Destination
1-May	Place_A	Destination_A
1-May	Place_A	Destination_C
1-May	Place_A	Destination_B
1-May	Place_C	Destination_B
2-May	Place_J	Destination_B
2-May	Place_A	Destination_A
2-May	Place_A	Destination_B
2-May	Place_F	Destination_B
3-May	Place_I	Destination_A
3-May	Place_A	Destination_D
3-May	Place_A	Destination_C
3-May	Place_A	Destination_B
3-May	Place_C	Destination_B
4-May	Place_A	Destination_A
4-May	Place_A	Destination_B
4-May	Place_B	Destination_A
4-May	Place_E	Destination_B
4-May	Place_G	Destination_B

[v-juanli-msft]

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]

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 · Accepted solution]

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

[xxenoss]

Thank you Maggie, this is brilliant, 

[v-juanli-msft]

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]

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.