Creating a latest occurrence column

[awff]

Hello fellow PBI'ers,

I am hoping to create a calculated column which identifies the latest occurrence with each account based on a few criterias. The result is as per the "Desired_Result" column in the mock table below:

Task:

• For each account, Identify the latest maintenance occurrence by end date for each product. The result should be a string.

AccountName	EndDate	Family	Product	Desired_Result
Account 1	1-Jan-22	Maintenance	A	Latest
Account 1	1-Mar-21	Maintenance	A
Account 1	1-Jul-22	Software	A
Account 2	1-Apr-20	Software	B
Account 2	1-Apr-22	Software	C
Account 3	1-Sep-21	Maintenance	C	Latest
Account 4	1-Sep-22	Maintenance	A
Account 4	1-Nov-22	Maintenance	A	Latest
Account 4	1-Jan-21	Maintenance	B	Latest
Account 4	1-Aug-20	Software	C
Account 4	1-Aug-20	Maintenance	C	Latest

Would anyone be able to assist with this? I've tried the common SWITCH(TRUE() method but i can't seem to break it down to an products per account level...

Thanks!

[awff]

Amazing, thank you both @Jihwan_Kim  & @FarhanAhmed !  Both measures work as it should and has saved my hair. True life saver 🙂

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Desired Result CC =
IF (
NOT ( Data[Family] = "Maintenance" ),
BLANK (),
SWITCH (
TRUE (),
VAR currentaccountname = Data[AccountName]
VAR currentproduct = Data[Product]
VAR newtable =
FILTER (
Data,
Data[AccountName] = currentaccountname
&& Data[Product] = currentproduct
&& Data[Family] = "Maintenance"
)
VAR maxdate_newtable =
MAXX ( newtable, Data[EndDate] )
RETURN
Data[EndDate] = maxdate_newtable, "Latest"
)
)

[FarhanAhmed · Accepted solution]

Try this

_Latest Result Column= 

Var Acc = 'Latest Occurence'[AccountName]
Var Pro = 'Latest Occurence'[Product]
Var dt = CALCULATE(MAX('Latest Occurence'[EndDate  ]),FILTER('Latest Occurence','Latest Occurence'[AccountName]=Acc && 'Latest Occurence'[Product]=Pro && 'Latest Occurence'[Family]="Maintenance"))

RETURN
IF(dt='Latest Occurence'[EndDate  ] && 'Latest Occurence'[Family]="Maintenance","Latest","")