Forum Discussion

bininja's avatar
bininja
Icon for Helper I rankHelper I
6 years ago
Solved

Pick first value for each id based on date

Hi guys,

 

I am struggling to pick up the first value registered for a id based on the earliest date.

So for the customer below, a membership is first created on 01.01.2016 and the first activity on this membership is created on 01.02.2017. For this membership I only want the first activity created to show up instead of all the others below. Then for the second membership which is created on 13.02.2019 the first activity is on 15.02.2019. I want to filter the table to only show the first activity for this membership instead of all.

 

I basically want to pick the acitivity row with the date closest to the membership created date for each customer id.


How can I achieve this in dax?

 

Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi bininja 

    Create measures

    Measure = DATEDIFF(MAX('Table'[created date]),MAX('Table'[activity date]),DAY)

Measure 2 =
RANKX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[id] = MAX ( 'Table'[id] )
            && 'Table'[created date] = MAX ( 'Table'[created date] )
    ),
    [Measure],
    ,
    ASC,
    DENSE
)
     
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi bininja 
    Try this:
    Latest Date =
CALCULATE ( MAX ( Table[Date] ), ALLEXCEPT ( Table, Table[ID] ) )
    Cheers!
    A
    • bininja's avatar
      bininja
      Icon for Helper I rankHelper I
      6 years ago

      Ashish_Mathur can this be done with a measure aswell? I see that you created a calculated column. I only have the possibility to create measures in the dataset as it is a live connection which limits alot of the options for me.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi bininja 

    Create measures

    Measure = DATEDIFF(MAX('Table'[created date]),MAX('Table'[activity date]),DAY)

Measure 2 =
RANKX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[id] = MAX ( 'Table'[id] )
            && 'Table'[created date] = MAX ( 'Table'[created date] )
    ),
    [Measure],
    ,
    ASC,
    DENSE
)
     
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.