Show Last Value based on Flag

[coding]

Hi all,

I have this table:

and I want to create a flag that shows the last value based on the last StartDate value and the last EndDate value to show me the last success status. 

The flag value "2" is correct, that is when it shows the last pipeline that is still in progress. But I wanted it to show the last status for the previous pipelines, in this case "Succeeded" for the last date of StartDate and EndDate. (the yellow lines would be the output I intended)

When the Pipeline2 ends and has the state of success, it will define the value "3" and the next pipeline that will be InProgress will have the value "2"

The Flag formula (calculated column):

Flag = 
SWITCH (
    TRUE (),
        'Table'[Status] = "Succeeded" &&
         MAXX (
            SUMMARIZE (
                'Table',
                'Table'[PipelineName]
            ),
            MAX ( 'Table'[EndDate] )
        )
        && MAXX (
            SUMMARIZE (
                'Table',
                'Table'[PipelineName]
            ),
            MAX ( 'Table'[StartDate] ))
            && YEAR( 'Table'[EndDate] ) <> 2999
        , 3,
         'Table'[StartDate] = MAX ( 'Table'[StartDate] )
        && 'Table'[EndDate] = BLANK ()
        && 'Table'[Status] = "InProgress", 2,
       0
)

The Source Table:

PipelineName	Status	StartDate	EndDate
Pipeline1	Succeeded	28/11/2022 10:12	28/11/2022 10:12
Pipeline1	InProgress	28/11/2022 10:12	31/12/2999 12:00
Pipeline1	Succeeded	28/11/2022 10:12	28/11/2022 10:26
Pipeline1	InProgress	28/11/2022 10:12	31/12/2999 12:00
Pipeline1	Succeeded	28/11/2022 10:12	28/11/2022 10:26
Pipeline1	InProgress	28/11/2022 10:12	31/12/2999 12:00
Pipeline1	Succeeded	28/11/2022 10:26	28/11/2022 10:30
Pipeline1	InProgress	28/11/2022 10:26	31/12/2999 12:00
Pipeline1	Succeeded	28/11/2022 10:26	28/11/2022 11:23
Pipeline1	InProgress	28/11/2022 10:26	31/12/2999 12:00
Pipeline1	Succeeded	28/11/2022 10:26	28/11/2022 10:30
Pipeline1	InProgress	28/11/2022 10:26	31/12/2999 12:00
Pipeline2	Succeeded	28/11/2022 11:35	28/11/2022 12:07
Pipeline2	InProgress	28/11/2022 11:35	31/12/2999 12:00
Pipeline2	Succeeded	28/11/2022 12:07	28/11/2022 12:17
Pipeline2	InProgress	28/11/2022 12:07	31/12/2999 12:00
Pipeline2	Succeeded	28/11/2022 12:17	28/11/2022 12:19
Pipeline2	InProgress	28/11/2022 12:17	31/12/2999 12:00
Pipeline2	InProgress	28/11/2022 12:19	31/12/2999 12:00

Can anyone please help me in achieving this?

Thank you and kind regards!

[v-stephen-msft · Accepted solution]

Hi @coding ,

For your case, it's suggested that you create an index column group by Pipeline name in Power Query first.

Steps of add the index column:

Click "Close&Apply" and go back to Power BI Desktop.

Then create a calculated column.

flag = var _max=CALCULATE(MAX('Table'[Index]),FILTER('Table',[PipelineName]=EARLIER('Table'[PipelineName])))
var _status=CALCULATE(MAX('Table'[Status]),FILTER('Table',[PipelineName]=EARLIER('Table'[PipelineName])&&[Index]=_max))
return IF(_max=[Index],SWITCH(_status,"Succeeded",3,"InProgress",2),0)

Best Regards,

Stephen Tao

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