Calculation duration over rows with condition

[Anonymous]

Hello,

I have been looking around on the forum for a solution on my query but I only found parts.

Below a part of my data set. I want to calculate the duration of a phase, under the condition of k_fin_out_header_id.

For the first key the status was changed from 1 to 2 on 27/02/2018 15:36 (= end date) (A)

Next it was changed from status 2 to 3 on 28/02/2018 16:19 (B) So duration of phase 2 was B -/- A

So in chronological order the duration needs to be calculated 

For the start date of phase 1 the value is presented in a separate column X

Example data

k_fin_out_header_id	value_old	value_new	created_date
001D5B3A-619D-4695-A95C-99393C2B46ED	1	2	10-4-2018 13:05
001D5B3A-619D-4695-A95C-99393C2B46ED	2	3	10-4-2018 13:21
001D5B3A-619D-4695-A95C-99393C2B46ED	3	4	10-4-2018 15:37
001D5B3A-619D-4695-A95C-99393C2B46ED	4	5	13-4-2018 16:16
001D5B3A-619D-4695-A95C-99393C2B46ED	5	6	16-4-2018 11:57
003AC412-1AC4-414C-BB0D-437240D8BB2F	2	10	14-3-2018 10:12
003AC412-1AC4-414C-BB0D-437240D8BB2F	10	11	19-3-2018 09:58
003AC412-1AC4-414C-BB0D-437240D8BB2F	1	2	14-3-2018 10:04
00721B41-9E38-40DD-8A3A-94EA164C04F1	2	11	14-2-2018 09:00
00721B41-9E38-40DD-8A3A-94EA164C04F1	1	2	13-2-2018 16:57
008196BB-E375-4235-ACEF-522797828E1E	1	2	31-1-2018 11:17
008196BB-E375-4235-ACEF-522797828E1E	4	3	1-2-2018 19:30
008196BB-E375-4235-ACEF-522797828E1E	2	4	31-1-2018 13:20
009B3688-120B-4601-8777-14BA71F9E36E	3	11	9-7-2018 11:49
009B3688-120B-4601-8777-14BA71F9E36E	1	2	9-7-2018 10:19
009B3688-120B-4601-8777-14BA71F9E36E	2	3	9-7-2018 10:38

I know that indexing may be a solution but I don't know how to make the index start over when a new k_fin_out_header shows up.

Thanks,

Leon

[v-yulgu-msft]

Hi @Anonymous,

Please new a calculated column:

duration =
DATEDIFF (
    LOOKUPVALUE (
        'Example Data'[created_date],
        'Example Data'[k_fin_out_header_id], 'Example Data'[k_fin_out_header_id],
        'Example Data'[value_new], 'Example Data'[value_old]
    ),
    'Example Data'[created_date],
    MINUTE
)

Best regards,

Yuliana Gu

[Anonymous]

Hi, 

Thanks for the solution.

However I am not sure if this will work when a status hits multiple times.

So for example

1-4  10:00

4-3  10:15

3-4  10:20

4-5  10:45

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

That case, my above solution would not work. And your solution with an extra Index column is much better.

Regards,

Yuliana Gu

[Anonymous]

I found a solution:

1: Sorted the table on fin_out_header_id

2.Sorted the table on creation date

3. Included an index column

4. added a lookupformula: lookupvalue(GenLog[End_date];GenLog[k_fin_out_header_id];GenLog[k_fin_out_header_id];GenLog[Index];GenLog[Index]-1) 

and the previous date is presented in a new colomn.

[Greg_Deckler]

Index is probably advisable. You need something like my MTBF calculation...See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Example data in text form is preferrable so that it can be copied and pasted. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Thanks for the suggestion however it is more challeging as I don't have a start date available of each phase. Only the end date is available.

I have added a sample of the data.