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
JRHans09
Resolver II
Resolver II

Date Difference between rows in single table, but matching data links in different columns

I have searched for similar posts, but have not found one that exactly matches this scenario.

 

We have a single data table on the database holding all the General Ledger transactions. I have split the table into two instances - 1 for invoices and receipts and 1 for purchases and payments. I want to calculate the date difference between the transaction post date for the invoice, versus the fully receipted date, or the post date for the purchase and the fully paid date.

 

The table has transaction date, transaction type, transaction ID, and applied ID columns. The applied ID column will have the same ID for a receipt, for example, as the original invoice transaction ID (this is the key link within the table).

 

I have a couple questions:

1. is it possible to just load 1 instance of the transaction table and use measures to calculate the date difference between invoice and receipt dates, as well as purchase and paid dates?

2. OR, is it best to separate the transaction table into 4 tables - invoices, receipts, purchases, and payments?

 

I would like to know how to create measures to perform the above so I can learn how to solve the above, even if creating 4 tables is easiest.

 

Here is a screen shot of an example of what our data table can look like, and what we need to do with the data:

 

Src is the Transaction Type:

DI - Debtor Invoice

DR - Debtor Receipt

CI - Creditor Invoice (not showing in example)

CP - Creditor Payment (not showing in example)

Collection Period.png

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@JRHans09 ,

 

"

1. is it possible to just load 1 instance of the transaction table and use measures to calculate the date difference between invoice and receipt dates, as well as purchase and paid dates?

2. OR, is it best to separate the transaction table into 4 tables - invoices, receipts, purchases, and payments?

"

 

Could you share the logic of date difference?(e.g.: Based on some specific condition) In addtion, could you clarify the logic of split into 4 tables?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuta-msft  - As indicated in the screen shot, I need to find the date difference between the original invoice date and the last receipt date that clears the balance to zero. The data table link between these two rows will be the Apply_ID. This will provide us with the timeliness of collection and timeliness of debtor payment to clear the balance.

 

Please see the screen shot. Thanks.

@v-yuta-msft  -

 

The logic of split into four tables -
The data originates from a single general ledger transaction table, containing all the different transaction types linked to the GL. In my first successful attempt at solving this, I have duplicated this single table into four tables and filtered each of these tables in the query stage by transaction type, thus, creating four separate tables -

Transaction types/resulting tables:

1. invoices

2. receipts

3. purchases

4. payments

 

This has made it easier to calculate the difference between the transactions dates since no calculations are required to link the transactions in the single table by Apply_ID. The link is made directly in the Manage Relationships process.

 

However, this requires loading the transaction table 4x during the query stage and I am wondering 2 things, as mentioned above:

1. can this be done within only 1 table?

2. is there a performance benefit of loading only 1 table, or does loading 4 tables originating from the same table OK?

2a. currently there are about 600K lines in the GL table. Over time, as that increases, I assume loading it 4x will reduce performance.

 

Hope this helps. Thanks.

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.