Compare two rows and return the one with the earliest date Power Bi

[MikiMihaela27]

Hi,

I have a table called "Jira tickets", in which I have multiple rows for the same ticket, as you can see below in the sample data:

If we take for example Ticket 1, how can I compare the rows to take only the first Sprint when this ticket was in status "Done", based on the Status date? In this case, to have a calculated column that will say "include" for Sprint 7 (08-Sep-22) and "exclude" for Sprint 8 (04-Oct-22). I will have to do the same thing for the status "To Do".

Thank you very much! 😊

[tamerj1 · Accepted solution]

Hi @MikiMihaela27 
Please try

Include/Exclude =
VAR CurrentStatus = 'Jira tickets'[Status]
VAR CurrentDate = 'Jira tickets'[Status Date]
VAR CurrentKeyTable =
    CALCULATETABLE (
        'Jira tickets',
        ALLEXCEPT ( 'Jira tickets', 'Jira tickets'[Key] )
    )
VAR FilteredTable =
    FILTER ( CurrentKeyTable, 'Jira tickets'[Status] = "Done" )
VAR FirstDone =
    MINX ( FilteredTable, 'Jira tickets'[Status Date] )
RETURN
    IF (
        CurrentStatus = "Done",
        IF ( CurrentDate = FirstDone, "Include", "Exclude" )
    )

[v-yadongf-msft · Accepted solution]

Hi @MikiMihaela27 ,

Please create following column:

Column = 
var min_date = MINX(FILTER('Table','Table'[Key] = EARLIER('Table'[Key]) && 'Table'[Status] = EARLIER('Table'[Status])),[Status Date])
var max_date = MAXX(FILTER('Table','Table'[Key] = EARLIER('Table'[Key]) && 'Table'[Status] = EARLIER('Table'[Status])),[Status Date])
var count_status = CALCULATE(COUNT('Table'[Status]),FILTER('Table','Table'[Key] = EARLIER('Table'[Key]) && 'Table'[Status] = EARLIER('Table'[Status])))
return
SWITCH(
    TRUE(),
    'Table'[Status Date] = min_date && count_status = 2, "include",
    'Table'[Status Date] = max_date && count_status = 2, "exclude"
)

I think this is the result you want:

Best regards,

Yadong Fang

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

[FreemanZ]

i tried, but unable to reach the last step, check if a row exists in another intermediate table.

the code for the intermediate table physically :

Table =

VAR table1 =

SUMMARIZE(

    JiraTickets,

    JiraTickets[Key],

    JiraTickets[Status]

)

VAR table2 =

ADDCOLUMNS(

    table1,

    "MinDate",

    CALCULATE(MIN(JiraTickets[StatusDate]))

)

RETURN Table2

[tamerj1 · Accepted solution]

Hi @MikiMihaela27 
Please try

Include/Exclude =
VAR CurrentStatus = 'Jira tickets'[Status]
VAR CurrentDate = 'Jira tickets'[Status Date]
VAR CurrentKeyTable =
    CALCULATETABLE (
        'Jira tickets',
        ALLEXCEPT ( 'Jira tickets', 'Jira tickets'[Key] )
    )
VAR FilteredTable =
    FILTER ( CurrentKeyTable, 'Jira tickets'[Status] = "Done" )
VAR FirstDone =
    MINX ( FilteredTable, 'Jira tickets'[Status Date] )
RETURN
    IF (
        CurrentStatus = "Done",
        IF ( CurrentDate = FirstDone, "Include", "Exclude" )
    )

[MikiMihaela27]

Hi @tamerj1 

Thank you for your message! It really helped me a lot! In fact, I adjusted a bit the logic to display "include/exclude" for both 'To Do' and 'Done' statuses:

IncludevsExclude =
VAR CurrentStatus = 'Jira tickets'[Status]
VAR CurrentDate = 'Jira tickets'['Jira tickets' Status Date]
VAR CurrentKeyTable =
CALCULATETABLE (
'Jira tickets',
ALLEXCEPT ( 'Jira tickets', 'Jira tickets'[Key])
)
VAR FilteredTableDone =
FILTER ( CurrentKeyTable, 'Jira tickets'[Status] = "Done" )
VAR FilteredTableToDo =
FILTER ( CurrentKeyTable, 'Jira tickets'[Status] = "To Do" )
VAR FirstDone =
MINX ( FilteredTableDone, 'Jira tickets' [Status Date])
VAR FirstToDo =
MINX ( FilteredTableToDo, 'Jira tickets' [Status Date])
RETURN
IF (
CurrentStatus = "Done",
IF ( CurrentDate = FirstDone, "Include", "Exclude" ),
IF(
CurrentStatus = "To Do",
IF ( CurrentDate = FirstToDo, "Include", "Exclude" )
))

Thanks again! 😊

[FreemanZ]

how to handle the status of In Progress?