Compare two dates from seprate table that are not directly connected

[Matthieu_R]

Hello guys,

I'm havig trouble while trying to create a measure that compare two dates from to fact tables that are not (obviously) connected. I will explain my schema.

First i have an invoice table that look like this :

invoice_id	creation_date	due_date	Amount	Number of payments expected	amount_payment1	amount_payment2	amount_payment3
1	01/04/2024	01/05/2024	20 €	2	10	10	null
2	02/04/2024	02/05/2024	30 €	1	30	null	null
3	03/04/2024	03/05/2024	25 €	3	10	10	5
4	04/04/2024	04/05/2024	35 €	1	35	null	null

Then i have a payment table that is filled automatically with the list of expected payments.

payment_id	invoice_id	payment_date	Amount_paid
1	1	30/04/2024	10 €
2	1	10/05/2024	10 €
3	2	30/04/2024	30 €
4	3	30/04/2024	10 €
5	3	10/05/2024	10 €
6	3	null	5 €
7	4	null	35 €

If i don't have receive the payments the payment date is null. Then that column is update when i receive the payment effectively.

Now my goal is to count the number of payment that are received before the due date of each invoice. Note that the due date is the maximum date where we should receive all payments of an invoice.

Both table are not linked directly together but are linked via a dimension table that contains the invoice_id, the due_date, the amount etc...

And also both dates (due_date and payment_date) are connected to a calendar table (inactive relationships)

One of the solution would be to put the due_date in the payment table for each line. I think it will make everything easyer.

But is it a good practice to have one column from a dimension table that is present in multiple fact table ?

Thanks in advance for your help.

[Matthieu_R]

will try that. Thanks

[jgeddes]

Here is an example code in case you need it...

let
    invoiceSource =
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc3dCcAgDEbRVUqeBfM7TXCZztOpOoka1LQPylU4fO5AUEArVUbWkZbJeL33M2Mcwn1BKx5fWjnVSdmK4jHBMhJG0pxkW0Z+SxZMg2myk2LfKdtTrQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [invoice_id = _t, creation_date = _t, due_date = _t, Amount = _t, #"Number of payments expected" = _t, amount_payment1 = _t, amount_payment2 = _t, amount_payment3 = _t]),
    invoiceTypeChange = 
    Table.TransformColumnTypes(invoiceSource,{{"invoice_id", Int64.Type}, {"creation_date", type date}, {"due_date", type date}, {"Amount", type text}, {"Number of payments expected", Int64.Type}, {"amount_payment1", Int64.Type}, {"amount_payment2", Int64.Type}, {"amount_payment3", Int64.Type}}),     
    paymentSource = 
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMTfWMDfSMDIxMQ30DhUdMapVidaCUjqLSpviE2aWMg1whNtzFCGszFbbgpVBqH4WZQaZAKuKA5yDiIoDFUNBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [payment_id = _t, invoice_id = _t, payment_date = _t, Amount_paid = _t]),
    paymentTypeChange = 
    Table.TransformColumnTypes(paymentSource,{{"payment_id", Int64.Type}, {"invoice_id", Int64.Type}, {"payment_date", type date}, {"Amount_paid", type text}}),
    mergeQueries = 
    Table.NestedJoin(paymentTypeChange, {"invoice_id"}, invoiceTypeChange, {"invoice_id"}, "invoiceTable", JoinKind.LeftOuter),
    expandDueDate = 
    Table.ExpandTableColumn(mergeQueries, "invoiceTable", {"due_date"}, {"due_date"}),
    addColumn = 
    Table.AddColumn(expandDueDate, "Payment Before Due", each try if [payment_date] < [due_date] then 1 else 0 otherwise 0, Int64.Type)
in
    addColumn

[jgeddes · Accepted solution]

My approach to this would be to merge due date into the payment table in Power Query and then create a custom column that tests if the payment date is less than the due date. If the results of that column were 1 for less than and 0 for not then your resulting measure would be the sum of that column. If you used 'yes' for less than and 'no' for not your resulting measure would be a conditional count.