Forum Discussion
Calculated colum - latest version of document
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 |
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/339586
5 Replies
camargos88Community Champion
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")- Greg_Deckler
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/339586
Thank you very much Greg! This really solved my issue.
- amitchandak
Super User
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos. 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.
