Forum Discussion
Get data diff in table based on other records in the same table
Hallo.
Clasic question.
I have I Incidents times - Start and End.
And I want to calculate system availability %.
I need Date diff between only those rows where are not overlapping with other records.
Exsample:
| Start_Time | End_time | Diff in days | Counted |
| 01.01.2023 | 04.01.2023 | 3 | yes |
| 06.01.2023 | 07.01.2023 | 1 | no |
| 05.05.2023 | 08.01.2023 | 3 | yes |
| Totall: | 6 |
I need to count only thouse, where counted is yes.
But how I can understand - how thouse dates from diferent rows are related each on other?
And I need create a measure.
Thanks in advance
6 Replies
Ho aldatinjsh
This subject is very complex. I guess your sample data is over simplified and does not present all possibilities. For example row 2 is completely contained in row 3 but what if they overlap but not entierly? Let's say I have row 4 which is 07.05.2023 to 10.01.2023 then what should be the expected result for each of the 4 rows.On the other hand other coluns in the table might be important, for example you have different ID's and a set of dates fro each ID
Am I overcomplicating the subject?You are write!
This is the real exsample!\
In SQL world I would joint this table with callendar table and then count only thouse distinct seconds, were is mach.. Maybe something form this side?!?!?!?
This requires concentration so mo chance today. No promises anyway. Meanwhile it would be great if you can share a sample file of 6-7 rows and manually indicate the expected result of each row
Hi. this ir my manaul calculation.
aldatinjsh
Please share this excel file. You can upload to any cloud service and share the link.
