Calculating the duration of application stages

[elmarthg]

Hello,

I  need some help on trying to figure this out. I want to calculate how many days before the submitted applications gets approved or denied and to see if the submitted applications are moving to the next stage or are just stuck in the same status.. Please guide me to the right direction. 

regardingobjectid	Applications	CreatedOn
00224803D300	Program 1 Submitted	11/1/2022 16:31
002248081105	Program 1 Denied	11/9/2022 22:16
002248081105	Program 1 Submitted	9/20/2022 22:06
00224803D300	Program 1 Submitted	1/21/2023 0:01
000D3A3367BD	Program 1 Submitted	12/23/2021 19:22
00224803D300	Program 2 Submitted	11/21/2022 17:22
00224803D300	Program 3 Submitted	11/21/2022 17:22
002248049AAA	Program 1 Approved	2/7/2023 21:52
002248049AAA	Program 1 Submitted	10/25/2022 18:30
00224803D300	Program 1 Approved	1/12/2023 0:10
00224803D300	Program 1 Submitted	11/1/2022 17:38
000D3A30ACEE	Program 2 Submitted	12/21/2022 21:26

[v-zhangti · Accepted solution]

Hi, @elmarthg 

You can try the following methods.

In the Power Query, sort the time column in ascending order and add the index column.

Column:

Previous time = 
CALCULATE (
    MAX ( 'Table'[CreatedOn] ),
    FILTER ( 'Table',
     [Regardingobjectid] = EARLIER ( 'Table'[Regardingobjectid] )
     &&[Index]<EARLIER('Table'[Index] ) ) )

Previous status = 
CALCULATE (
    MAX ( 'Table'[Applications] ),
    FILTER ( 'Table',
     [Regardingobjectid] = EARLIER ( 'Table'[Regardingobjectid] )
     &&[Index]<EARLIER('Table'[Index] ) ) )

Difference = DATEDIFF([Previous time],[CreatedOn],DAY)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

@elmarthg , Try a new column

var _max= maxx(filter(Table, [regardingobjectid] = earlier([regardingobjectid]) && [Applications] = earlier([Applications]) && [Status] ="Submitted"
&& [CreatedOn] < earlier([CreatedOn]) ), [CreatedOn])
return
if( [Status] in {"Approved", "Denied"}, datediff(_max, [CreatedOn], day), blank())

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8