Find last Product Rev - either Calculated Column or Measure

[Anonymous]

Hi Everyone,

I have been struggling to find a solution to finding that last time my Product Rev changed.  The goal is to have my Power BI show if the revision changed and so far all my attempts have not worked.

Here are the basic columns which have my details.

Version	Product	Prod Rev	Latest Rev Date
1/1/2023	Red	Rev 1	1/1/2023
1/1/2023	Orange	Rev 2	1/1/2023
1/1/2023	Black	Rev 1	1/1/2023
1/8/2023	Red	Rev 2	1/8/2023
1/15/2023	Red	Rev 2	1/8/2023
1/15/2023	Orange	Rev 2	1/1/2023
1/22/2023	Orange	Rev 3	1/22/2023
2/2/2023	Orange	Rev 3	1/22/2023
2/2/2023	Black	Rev 1	1/1/2023
2/9/2023	Black	Rev 2	2/9/2023
2/16/2023	Black	Rev 2	2/9/2023
2/16/2023	Red	Rev 3	2/16/2023
2/23/2023	Red	Rev 3	2/16/2023
2/23/2023	Black	Rev 2	2/9/2023
3/2/2023	Black	Rev 3	3/2/2023
3/9/2023	Black	Rev 3	3/2/2023

You can see in the table the Latest Rev Date stays with the Product Versions (report dates) till the Prod Rev changes again. 

I think a calculated column makes the most sense because my dataset it several million rows and I am worried about load time, but I could work with a measure probably.

Thanks!

Matt

[tamerj1 · Accepted solution]

Hi @Anonymous 

in your example you are showing the minimum version for the same Product/Prod Rev. but you asking for the last? Please clarify. Otherwise, to produce the same results as per the provided sample and results you may try a calculated column 

Latest Rev Date =
CALCULATE ( MIN ( 'Table'[Version] ), ALLEXCEPT ( 'Table', 'Table'[Product], 'Table'[Prod Rev] ) )

[Anonymous · Accepted solution]

Hi @tamerj1 ,

You got it perfect!  Sorry about the mix up in min and max.  Min was what I was looking for.  

Thank you so much!

[tamerj1 · Accepted solution]

Hi @Anonymous 

in your example you are showing the minimum version for the same Product/Prod Rev. but you asking for the last? Please clarify. Otherwise, to produce the same results as per the provided sample and results you may try a calculated column 

Latest Rev Date =
CALCULATE ( MIN ( 'Table'[Version] ), ALLEXCEPT ( 'Table', 'Table'[Product], 'Table'[Prod Rev] ) )