Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aldatinjsh
Frequent Visitor

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_TimeEnd_timeDiff in daysCounted
01.01.202304.01.2023  3yes
06.01.202307.01.2023  1no
05.05.202308.01.2023  3yes
 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 6
aldatinjsh
Frequent Visitor

aldatinjsh_1-1679644854749.png

 

Hi. this ir my manaul calculation.

@aldatinjsh 
Please share this excel file. You can upload to any cloud service and share the link.

tamerj1
Super User
Super User

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!\

 

aldatinjsh_0-1679572142741.png

 

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?!?!?!?

@aldatinjsh 

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

Private massage.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors