Setting a field to null based on another field's value

[Anonymous]

Hey Everyone,

Background:

I'm pulling team sprint data from Jira cloud and want to show what stories the team completed within a sprint. This is easy to do when a story is completed within a single sprint since a single row is created. The problem I'm running into occurs when a story appears in multiple sprints because another row will be created. Except for the sprint field, this row will be identical to the other, including the completed date. This means that once a story is completed, it will appear as completed in every sprint it was in (see below).

Story # | Sprint | Completed Date

     1      |      4      |   2018-11-15

     1      |      5      |   2018-11-15

     1      |      6      |   2018-11-15

Question:

To solve this I need to clear the completed date field so that it's null for every sprint other than the last sprint it appears in (see below). 

Story # | Sprint | Completed Date

     1      |      4      |   null

     1      |      5      |   null

     1      |      6      |   2018-11-15

Anyone know how to do this?

Thank you!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

You can add a calculated column.

Completeed Date2 =
IF (
    TB3[Sprint] = CALCULATE ( MAX ( TB3[Sprint] ), ALLEXCEPT ( TB3, TB3[Story] ) ),
    TB3[Completed Date],
    BLANK ()
)

Best regards,

Yuliana Gu