Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Hi @Anonymous,
That case, my above solution would not work. And your solution with an extra Index column is much better.
Regards,
Yuliana Gu
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |