Get first date of column based on criteria

[thainonne]

Hi community,

I checked many posts now but could not get an answer to the following:

I am trying to create a calculated column which shows only the first date of a related column in the same table in the respective row which contains the first value. Even if there is a second row which also contains the first date, it should be shown as blank because it would be a duplicate.

I have tried to create one with MINX, MIN, TOPN etc. but it either showed the first column of the whole dataset or shows the date if it is not blank but does not react to any further filter criteria I have applied in DAX.

Table:

Column "Requisition ID" and "Screen Date" exist and the third column "First screen date" is the calculated column I want to create.

Many thanks in advance,

Marvin

Requisition ID	Screen Date (DD.MM.YY)	First screen date (DD.MM.YY)
R1	01.01.21	01.01.21
R1	01.01.21
R2	03.02.21	03.02.21
R3	05.01.21	05.01.21
R1	12.02.21
R2	22.02.21
R4	17.02.21	17.02.21
R1	18.01.21
R3	02.02.21

[v-yanjiang-msft · Accepted solution]

Hi @thainonne ,

According to your description, here's my solution.

1.Add an index column in Power Query.

2.Create a calculated column:

Column =
VAR _MINDATE =
    MINX (
        FILTER (
            'Table',
            'Table'[Requisition ID] = EARLIER ( 'Table'[Requisition ID] )
        ),
        'Table'[Screen Date (DD.MM.YY)]
    )
VAR _MINDEX =
    MINX (
        FILTER (
            'Table',
            'Table'[Requisition ID] = EARLIER ( 'Table'[Requisition ID] )
                && 'Table'[Screen Date (DD.MM.YY)] = EARLIER ( 'Table'[Screen Date (DD.MM.YY)] )
        ),
        'Table'[Index]
    )
RETURN
    IF (
        [Screen Date (DD.MM.YY)] = _MINDATE
            && [Index] = _MINDEX,
        [Screen Date (DD.MM.YY)]
    )

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[thainonne]

@v-yanjiang-msft  many thanks - this solved the problem. 

Cheers and again thank you!

[AjithPrasath]

@thainonne ,

  Hi, can you try the below code?

FirstScreenDate =
VAR CurrentRequisitionID = 'YourTable'[Requisition ID]
VAR MinDate =
    CALCULATE(
        MIN('YourTable'[Screen Date (DD.MM.YY)]),
        ALLEXCEPT('YourTable', 'YourTable'[Requisition ID])
    )
RETURN
    IF('YourTable'[Requisition ID] = CurrentRequisitionID && 'YourTable'[Screen Date (DD.MM.YY)] = MinDate, MinDate, BLANK())

Please let me know if you have any issues

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[thainonne]

@AjithPrasath Many thanks for the quick response.

I have tried your solution and it does pull the first date. However, it still shows multiple rows if e.g. three rows for the same Requisition ID have the same screen date.

I need only the first occuring row so to say even if the same date comes up a second, third,... time for the same Requisition ID.

Is this something which can also be entered in the DAX formula?

Thank you