Confused - iterating over values

[Parkyerbike]

Hello, I am after some help if possible.

I have a table, that contains a list of unique customer names. I have another table that contains details about that customer, this has a list of installations (not unique) and what I would really like to do, is create a column in customer name, that has the latest version of the software, based on date and subsys where the environment is set to production

So, as an example, I would like the customer table to end up something like this, with an entry for each customer, based on the latest date from another table, based on subsys = CR and environment = production

Customer Table - Key = ID

Patch Table - Key is IdentifiedInstallationID

So I am thinking I would need to iterate over the dates, per IdentifiedInstallationID where SubSys = CR and Environment = production and bring back the latest entry

Thanks in advance

[Parkyerbike]

ID	Customer Name	Software Version (Trying to generate this)
1	ABC Co	Version 3
2	Fred Bloggs	Version 2
3	A Company
4	B Company
5	C Company

Trying to create the above column, based on sample data below

Where the Environment = Production, the Subsys is CR and the latest date in the list per ID

ID	Environment	Date Patch Applied	Subsys	Software Name
1	Production	1/2/24	CR	Version 1
1	Production	3/2/24	CR	Version 2
1	Production	5/2/24	CR	Version 3
1	Staging	3/2/24	CR	Version 4
2	Production	3/2/24	AB	Version 4
2	Production	5/2/24	CR	Version 2

Hope that helps

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

Enter this calculated column formula in Table1

Column = LOOKUPVALUE(Table2[Software Name],Table2[Date Patch Applied],CALCULATE(MAX(Table2[Date Patch Applied]),FILTER(Table2,Table2[ID]=EARLIER(Table1[ID])&&Table2[Environment]="Production"&&Table2[Subsys]="CR")),Table2[ID],Table1[ID])

Hope this helps.

[Ritaf1983]

Hi @Parkyerbike 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523