topic Re: Need help to make calculations based on a selection of matching rows in a related table in DAX Commands and Tips

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

larsm11 — Sun, 01 Mar 2020 01:34:28 GMT

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.

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

Mariusz — Thu, 05 Mar 2020 10:39:35 GMT

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

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

Anonymous — Thu, 05 Mar 2020 11:59:20 GMT

[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