Need help to make calculations based on a selection of matching rows in a related table

[larsm11]

Hi - I'm relatively new to DAX, and I have the following issue that I cannot get passed.

I have a table with invoices, and an related table (they share InvoiceID) with all ledger postings related to these invoices.

I want to create a custom column in the Invoice table with payment days (using DAX) based on the following rationale:

• Find all postings in the Postings table with matching invoiceID as the row in the invoice table (e.g. 77536391 in the sample)
• Calculate the days between date of the posting with the highest positive Amount value and the matching posting with the highest negative amount
• Add the calculated number to the Payment Days custom column in the Invoice table

Hopeful someone can help.

/LarsM

Invoice and Postings table extract.

Invoice table extractPostings table extract

[Anonymous]

[Payment Days] =  // calc column
VAR __maxAmount = 
	MAXX(
		RELATEDTABLE( Postings ),
		Postings[Amount]
	)
var __minAmount =
	MINX(
		RELATEDTABLE( Postings ),
		Postings[Amount]
	)
var __amountsAreCorrect =
	__maxAmount > 0
	&& ( __maxAmount + __minAmount = 0 )
var __dayOfMaxAmount =
	CALCULATE(
		VALUES( Postings[Date] ),
		Postings[Amount] = __maxAmount
	)
var __dayOfMinAmount =
	CALCULATE(
		VALUES( Postings[Date] ),
		Postings[Amount] = __minAmount
	)
var __onlyOnePairOfDatesFound =
	COUNTROWS( __dayOfMinAmount ) = 1
	&&
	1 = COUNTROWS( __dayOfMaxAmount )
var __shouldCalculate =
	__amountsAreCorrect && __onlyOnePairOfDatesFound
RETURN
if( __shouldCalculate, int(__dayOfMaxAmount - __dayOfMinAmount) )

This should cover all the problems you might have in the Postings table. For instance, there could not be a matching negative amount.. and the likes.

Best

D

[Mariusz]

Hi @larsm11 

Try this 

Column = 
VAR __amounts = 
    CALCULATETABLE( 
        {
            MIN( Postings[Amount] ),
            MIN( Postings[Amount] ) 
        }
    )
RETURN 
CALCULATE(
    DATEDIFF( MIN( Postings[Date] ), MAX( Postings[Date] ), DAY ),
    TREATAS( __amounts, Postings[Amount] )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn