Forum Discussion

xxenoss's avatar
xxenoss
Icon for Helper I rankHelper I
8 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 p...
  • v-juanli-msft's avatar
    v-juanli-msft
    8 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

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 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