Last Modified Time

[surajde]

HI All, 

Need your help with BI Formula for below dummy data. 
In excel it was much easy. Need to push this to PowerBI. Please help with below scenario to replicate this in PowerBi

if you copy this to Excel use below formula in Current Group Time Column: 

=IF(A2<>A1, E2, IF(B2<>B1, E2, F1))

Expected result should be as per Current Group Time. 

Request ID	Group Name	Analyst	Request Created on	Request Modified on	Current Group Time
K1122334	MOD Tier 1	-	5/1/23 10:32 AM	5/1/23 10:32 AM	5/1/23 10:32 AM
K1122334	MOB IT PPET	-	5/1/23 10:32 AM	5/1/23 10:48 AM	5/1/23 10:48 AM
K1122334	IT_Q2A_E2E	-	5/1/23 10:32 AM	5/1/23 12:01 PM	5/1/23 12:01 PM
K1122334	IT_Q2A_E2E	Shawn Trieloff	5/1/23 10:32 AM	5/1/23 12:36 PM	5/1/23 12:01 PM
C2244666	MOD Tier 1	-	5/1/23 10:55 AM	5/1/23 10:55 AM	5/1/23 10:55 AM
C2244666	MOD Tier 1	-	5/1/23 10:55 AM	5/1/23 10:57 AM	5/1/23 10:55 AM
C2244666	MOD Tier 1	Neelam Seth	5/1/23 10:55 AM	5/1/23 11:11 AM	5/1/23 10:55 AM
C2244666	MOD Tier 1	Neelam Seth	5/1/23 10:55 AM	5/1/23 11:50 AM	5/1/23 10:55 AM
D6677888	MOD Tier 1	-	5/1/23 1:00 PM	5/1/23 1:00 PM	5/1/23 1:00 PM
D6677888	MOD Tier 1	Anand Mane	5/1/23 1:00 PM	5/1/23 1:35 PM	5/1/23 1:00 PM
D6677888	MOD Tier 1	Neelam Seth	5/1/23 1:00 PM	5/1/23 4:54 PM	5/1/23 1:00 PM
D6677888	MOD Tier 1	Neelam Seth	5/1/23 1:00 PM	5/1/23 4:55 PM	5/1/23 1:00 PM
D6677888	MOD Tier 1	-	5/1/23 1:00 PM	5/5/23 12:45 PM	5/1/23 1:00 PM
D6677888	BMG Management	-	5/1/23 1:00 PM	5/5/23 12:58 PM	5/5/23 12:58 PM
D6677888	BMG Management	Courtney Morgan	5/1/23 1:00 PM	5/10/23 4:37 PM	5/5/23 12:58 PM
D6677888	MOD Tier 1	-	5/1/23 1:00 PM	5/16/23 1:59 PM	5/16/23 1:59 PM
D6677888	MOD Tier 1	Anand Mane	5/1/23 1:00 PM	5/16/23 2:35 PM	5/16/23 1:59 PM
D6677888	MOD Tier 1	Abhishek Mishra	5/1/23 1:00 PM	5/16/23 4:00 PM	5/16/23 1:59 PM
D6677888	MOD Tier 1	Bheemsen Dasnurkar	5/1/23 1:00 PM	5/17/23 12:59 AM	5/16/23 1:59 PM
D6677888	MOD Tier 1	Bheemsen Dasnurkar	5/1/23 1:00 PM	5/17/23 1:05 AM	5/16/23 1:59 PM
J4357593	MOD Tier 1	-	5/2/23 1:52 AM	5/2/23 1:52 AM	5/2/23 1:52 AM
J4357593	MOD Tier 1	Bheemsen Dasnurkar	5/2/23 1:52 AM	5/2/23 1:56 AM	5/2/23 1:52 AM
J4357593	FI Quality	-	5/2/23 1:52 AM	5/2/23 8:51 AM	5/2/23 8:51 AM
J4357593	FI Quality	Rahul Pawar	5/2/23 1:52 AM	7/28/23 2:03 PM	5/2/23 8:51 AM

[Anonymous · Accepted solution]

Hi @surajde ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2)Click on "transform data" to enter the power query and add index a column.

(3) We can create calculated columns.

Column = MAXX(FILTER('Table',[Request ID]=EARLIER('Table'[Request ID])&&[Index]=EARLIER('Table'[Index])-1),[Group Name])

Column 2 = IF(OR([Column]=BLANK(),[Group Name]<>[Column]),[Request Modified on ])

Column 3 = var a=MAXX(FILTER('Table',[Request ID]=EARLIER('Table'[Request ID])&&[Group Name]=EARLIER('Table'[Group Name])&&[Index]<EARLIER('Table'[Index])&&[Column 2]<>BLANK()),[Index])
return IF([Column 2]<>BLANK(),[Column 2],IF([Group Name]=[Column],LOOKUPVALUE('Table'[Column 2],'Table'[Index],a)))

(4) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.