topic Re: MEASURE for calculating DueDate of invoice (two tables) in DAX Commands and Tips

MEASURE for calculating DueDate of invoice (two tables)

michellepace — Tue, 15 Sep 2020 08:23:38 GMT

Hi there,

Below is what my data model looks like. I would like to write a measure to calculated DueDate. If this is not possible, I'd appreciated if somebody can explain to me why? From help on other forums, I have been able to create a calculated column to create DueDate. I have also been shown how to do a table merge in Power Query (to get PaymentTerms) and then create a calculated column there.

Is it possible to write a measure for DueDate (and if not, why not)?

Re: MEASURE for calculating DueDate of invoice (two tables)

amitchandak — Tue, 15 Sep 2020 04:33:10 GMT

michellepace , it should possible. Will work with row context

The first thing try to push this calculation into a sumx or AverageX

Sumx(Debtorpayable,<Formula>)

another option is to have min for min date and minx(Debtorpayable, related(customerpayable[payterm])) in the same formula

Re: MEASURE for calculating DueDate of invoice (two tables)

michellepace — Tue, 15 Sep 2020 08:20:21 GMT

Hello amitchandak Thank you for your reply. I have tried your suggestions (as depicted below) but somehow I'm still not enforcing row context. Also what I am a bit confused about, is that this is a date calculation. So using SUMX ... wouldn't that always return a number?

> The first thing try to push this calculation into a sumx or AverageX
Nope, this doesn't work for me. Below I first try to ensure I get the correct value for payment Terms. Some invoices should say 30, some 60... but none 570. Obviously adding things up.

> another option is to have min for min date and minx(Debtorpayable, related(customerpayable[payterm]))
Nope, this doesn't work for me either. The payment term which gets returned is then "30" for all invoices.

Question: I would like to know how to write the DAX formula for calculating due date. I obviously need row context as pointed out by Amit. Could someone please help me? And also, if it it is not too much to ask, explain the formula and how it works. I really need to get my head wrapped around this DAX. I think I may be missing an entire paradigm... or two or three!

Thanks very much 🙂

Michelle