New custom column to get the latest status of release lifecycle by comparing rows in the database

[malpani]

Hi, 

In my datasource (JIRA), I am getting data in following format. 

This datasource shows release data which goes through milestones from P1 to P10 and status of each milestone.

Unique Key	Product Name	Release Name	Milestone	Status
Key1	Product A	24.x	P1	Approved
Key2	Product A	24.x	P2	Approved
Key3	Product A	24.x	P3	Approved
Key4	Product A	24.x	P4	Approved
Key5	Product A	24.x	P5	Approved
Key6	Product A	24.x	P6	Approved
Key7	Product A	24.x	P7	Approved
Key8	Product A	24.x	P8	Planned
Key9	Product A	24.x	P9	Planned
Key10	Product A	24.x	P10	Planned
Key11	Product B	24.x	P1	Approved
Key12	Product B	24.x	P2	Approved
Key13	Product B	24.x	P3	Approved
Key14	Product B	24.x	P4	Planned
Key15	Product B	24.x	P5	Planned
Key16	Product B	24.x	P6	Planned
Key17	Product B	24.x	P7	Planned
Key18	Product B	24.x	P8	Planned
Key19	Product B	24.x	P9	Planned
Key20	Product B	25.x	P10	Planned

I would like to get an additional column to get the latest lifecycle status of release meaning which latest milestone is Approved. 

Unique Key	Product Name	Release Name	Milestone	Status	Lifecycle Status
Key1	Product A	24.x	P1	Approved	P5 Approved
Key2	Product A	24.x	P2	Approved	P5 Approved
Key3	Product A	24.x	P3	Approved	P5 Approved
Key4	Product A	24.x	P4	Approved	P5 Approved
Key5	Product A	24.x	P5	Approved	P5 Approved
Key6	Product A	24.x	P6	Approved	P5 Approved
Key7	Product A	24.x	P7	Approved	P5 Approved
Key8	Product A	24.x	P8	Planned	P5 Approved
Key9	Product A	24.x	P9	Planned	P5 Approved
Key10	Product A	24.x	P10	Planned	P5 Approved
Key11	Product B	24.x	P1	Approved	P3 Approved
Key12	Product B	24.x	P2	Approved	P3 Approved
Key13	Product B	24.x	P3	Approved	P3 Approved
Key14	Product B	24.x	P4	Planned	P3 Approved
Key15	Product B	24.x	P5	Planned	P3 Approved
Key16	Product B	24.x	P6	Planned	P3 Approved
Key17	Product B	24.x	P7	Planned	P3 Approved
Key18	Product B	24.x	P8	Planned	P3 Approved
Key19	Product B	24.x	P9	Planned	P3 Approved
Key20	Product B	25.x	P10	Planned	P3 Approved

[Ashish_Mathur]

Hi,

The answer should be P7 (not P5) for Product A.  This calculated column formula works

Column = CALCULATE(MAX(Data[Milestone]),FILTER(Data,Data[Product Name]=EARLIER(Data[Product Name])&&Data[Status]="Approved"))

[v-kongfanf-msft · Accepted solution]

Thanks for the reply from @amitchandak , please allow me to provide another insight:

Hi @malpani ,

You can try formula like below:

Lifecycle Status =
VAR CurrentProduct = Releases[Product Name]
VAR CurrentRelease = Releases[Release Name]
VAR LatestApprovedMilestone =
    CALCULATE (
        MAX ( Releases[Milestone] ),
        FILTER (
            Releases,
            Releases[Product Name] = CurrentProduct
                && Releases[Release Name] = CurrentRelease
                && Releases[Status] = "Approved"
        )
    )
RETURN
    IF (
        ISBLANK ( LatestApprovedMilestone ),
        BLANK (),
        LatestApprovedMilestone & " Approved"
    )

Best Regards,
Adamk Kong

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

[amitchandak]

@malpani , Create a new column

Stage = right([Milestone], Len([Milestone]) -1)

Then create a measure

final status =

var _max = maxx(filter(Table, [Product Name] = earlier([Product Name]) ), [Stage])

return

maxx(filter(Table, [Product Name] = earlier([Product Name]) && [Stage] =_max) , [Status])

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8