Calculated colum - latest version of document

[petersoelberg]

Hi! I have been searching for a similar case for a couple of days but I am unable to find a relatable solution so I hope one of you will be abe to help me with my enquiry. 

I have a sales order archive, where i only want to use the data from the latest version of the document. 

I hope the following data will illustate the data model - I want to create a calculated column (Like i have manually created in the example below) which I can filter, so i only see the latest version. (There are more columns to the data model, i just haven't presented it - but that is why I only want to view the latest entry..)

Document no	Version no.	Calculated Column!
MO0005599	1	Archived
MO0005599	2	Archived
MO0005599	3	Archived
MO0005599	4	Archived
MO0005599	5	Archived
MO0005599	6	Archived
MO0005599	7	Latest
MO0003234	1	Latest
MO0003221	1	Archived
MO0003221	2	Archived
MO0003221	3	Archived
MO0003221	4	Latest
MO0007472	1	Archived
MO0007472	2	Archived
MO0007472	3	Archived
MO0007472	4	Latest

[Anonymous]

@petersoelberg 

You could just use Allexcept(): 

Column = 
var maxversion = CALCULATE(MAX('Table'[Version no.]),ALLEXCEPT('Table','Table'[Document no]))
Return IF([Version no.]=maxversion,"Latest","Archived")

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@petersoelberg , Refer on of the two. My favorite is LASTNONBLANKVALUE these days

new Column = if(Table1[Version no.]= LASTNONBLANKVALUE(Table1[Document no],max(Table1[Version no.])),"Latest","Archived")
new Column =
  if([Version no] = maxx(filter(Table,[Document no] = earlier([Document no])),[Version no]),"Latest","Archived")

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[Greg_Deckler · Accepted solution]

@petersoelberg - This is basic use of EARLIER:

Column =
  VAR __Max = MAXX(FILTER('Table',[Document no] = EARLIER([Document no])),[Version no])
RETURN
  IF([Version no] = __Max,"Latest","Archived")

For more complex versions, See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[petersoelberg]

Thank you very much Greg! This really solved my issue. 

[camargos88]

Hi @petersoelberg ,

Try this code:

Column =

VAR _maxVersion = CALCULATE(MAX('Table'[Version no.]), FILTER('Table', 'Table'[Document no] = EARLIER('Table'[Document no])))

RETURN IF('Table'[Version no.] = _maxVersion, "Latest", "Archived")