Pick first value for each id based on date

[bininja]

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?

[v-juanli-msft · Accepted solution]

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.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[bininja]

@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.

[Ashish_Mathur]

Hi,

I am not sure of how to solve this question with a measure.

[Anonymous]

Hi @bininja 

Try this:

Latest Date =
CALCULATE ( MAX ( Table[Date] ), ALLEXCEPT ( Table, Table[ID] ) )

Cheers!
A