Forum Discussion

xxenoss's avatar
xxenoss
Helper 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
    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

v-juanli-msft's avatar
v-juanli-msft
Community 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

  • xxenoss's avatar
    xxenoss
    Helper I
    8 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

      • xxenoss's avatar
        xxenoss
        Helper I
        7 years ago

        Thank you Maggie, this is brilliant,