How to mark the latest baseline per project

[CobusEllis]

Hi,

I'm working on data from MS Projects.
A project can have multiple baselines, I need the first and the last.
The first one is easy as the baseline number is 0.
However I'm having trouble building a formula to mark per Project per Baseline what is the latest Baseline Number. With that flag I can add a filter etc.

Current data structure for baselines:
ProjectId, BaselineNumber, (metadata)
Sample data:
Proj1, 0, ...
Proj1, 1, ...
Proj1, 2, ...
Proj2, 0, ...
Proj3, 0, ...
Proj3, 1, ...

I need to add a formula that will enter the data as such:
ProjectId, BaselineNumber, IsLatest, (metadata)
Proj1, 0, 0 ,...
Proj1, 1, 0 ,...
Proj1, 2, 1 ,...
Proj2, 0, 1 ,...
Proj3, 0, 0 ,...
Proj3, 1, 1 ,...

Very Important, this must happen in the Query editor!

Please let me know if you have any questions!


Thank you in advance,
Cobus

[CobusEllis]

Thanks for all the helpful replies!

[v-piga-msft]

Hi @CobusEllis,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[LivioLanzo · Accepted solution]

You can easily do it like this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzzJU0lEyUIrVQfAMUXhGcJ4RikpjDB5QXywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Baseline = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Baseline", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {{"Mx", each List.Max([Baseline]), type number}}),
    Custom1 = Table.NestedJoin(#"Changed Type", {"Project"}, #"Grouped Rows", {"Project"}, "Latest", JoinKind.Inner),
    #"Added Custom" = Table.AddColumn(Custom1, "IsLatest", each if [Latest]{0}[Mx] = [Baseline] then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Latest"})
in
    #"Removed Columns"

[PattemManohar]

@CobusEllis If you would like to do it in DAX then,

IsLatest = 
VAR _maxIndex = CALCULATE(MAX(FlagLatest[Index]),FILTER(ALL(FlagLatest),FlagLatest[ProjectID]=EARLIER(FlagLatest[ProjectID])))
RETURN IF(FlagLatest[Index] = _maxIndex,1,0)

Note - I've created an Index Column in Power Query.

Here is the expected output...

[Greg_Deckler]

Ah, if this was DAX it would be easy using MAXX. I'm sure that @ImkeF knows of a way in M though.