Forum Discussion
Find a delay with multiples variables and lines
Hello Power BI community,
I have a tough problem that I couldn't solve.
You can find below an exemple of my problem.
An ID is a workflow. I need to find the delay each time I have a status between the line of the status and the oldest line without a status, considering that the delay start again to the next line when
| ID | Time | Status | Delay |
| ABC | 01/07/2020 04:07 | ||
| ABC | 02/07/2020 04:15 | ||
| ABC | 03/07/2020 04:14 | ||
| ABC | 06/07/2020 04:00 | ||
| ABC | 06/07/2020 06:27 | Done | 5,09670 |
| XYZ | 11/06/2020 04:03 | ||
| XYZ | 12/06/2020 04:02 | To check | 0,99929 |
| XYZ | 15/06/2020 04:02 | ||
| XYZ | 16/06/2020 04:04 | Transfered | 1,00122 |
| XYZ | 17/06/2020 04:14 | ||
| XYZ | 18/06/2020 04:14 | Cancelled | 1 |
| QRS | 01/09/2020 04:10 | In progress | 0 |
| MNO | 07/09/2020 04:09 | ||
| MNO | 07/09/2020 09:49 | Done | 0,23596 |
As you can see to find the first delay (ABC), I did [06/07/2020 06:27] - [01/07/2020 04:07] = 5,09670
This is easy to do with Dax.
My problem is when I have multiple delay to calculate like XYZ :
To find the 1st delay (XYZ), I need to do : [12/06/2020 04:02]- [11/06/2020 04:03] = 0,99929
To find the 2nd delay (XYZ), I need to do : [16/06/2020 04:04] - [15/06/2020 04:02] = 1,00122
To find the 3rd delay (XYZ), I need to do : [18/06/2020 04:14] - [17/06/2020 04:14] = 1
I don'k know how to create a DAX code that can calculate theses values. If I always use the most ancient date, it would have been easy. But because the delay start over again from the next line to the next status, it is difficult to find a formula for this.
Thank you for your help.
Best regards,
Alex
Hi, Anonymous
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file, and all steps are numbered in front of each measure.
https://www.dropbox.com/s/fma9hsrn4xiz09h/alexw.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
Hi, Anonymous
Please check the below link, whether it is what you are looking for.
Calculated Measure and Calculated Column are different and have to be written in a different way.
https://www.dropbox.com/s/fma9hsrn4xiz09h/alexw.pbix?dl=0
5 Replies
- Jihwan_Kim
Super User
Hi, Anonymous
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file, and all steps are numbered in front of each measure.
https://www.dropbox.com/s/fma9hsrn4xiz09h/alexw.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
Hi,
I'm opening this problem again because I need to have 4 delay in a column because I need to use these data for an another formula and graph. However, it's impossible. I tried to reproduce these measures in a column but I have a circular dependency. How can I avoid it please ?
Thank you for your help,
Best regards,
Alex
- Jihwan_Kim
Hi, Anonymous
Please check the below link, whether it is what you are looking for.
Calculated Measure and Calculated Column are different and have to be written in a different way.
https://www.dropbox.com/s/fma9hsrn4xiz09h/alexw.pbix?dl=0