Lookup value in another table to calc DUE DATE

DAX DAX Calculations
1

Hi there,

Could someone please help me to write the DAX statement for DueDate = MinDate + Payment Terms as per the below model? It really amazes that I find this so difficult. I must be missing the entire DAX paradigm.

screenshot

Thanks very much in advance.
Michelle

2

Hi @michellepace,

Not everything is ment to be solved with DAX… Why not calculate the Due date in Power Query?

  • Do a Merge with DebtorCustomers to get the PaymentTerms
  • Add a Custom Column to your DebtorPayables, something like:
    Date.AddDays( [MinDate], [PaymentTerms] )

I hope this is helpful.

2 Likes
3

Hi Melissa, thank you - your reply is helpful. Goodness, how frustrating. I have been trying to write the dax for this for the past 2 hours and now to find that it is not possible Ergg. Thank you for your prompt reply :slight_smile:

Now, what about a calculated column? Is that somehow possible? This is as far as I’ve got… is there any obvious reason why I don’t have a value in every cell of DueDateXX ?

2

4

Hello michellepace,

you can try to create a new column from Dates tab: go to new column and write DueDate = MinDate + RELATED ( PaymentTerms)

Hope it’s helpful

5

Hi @michellepace,

You can use RELATED as @Matteo-Italy suggested for a calculated column but it’s best to push this calculation as far back to the source as you can, in this case my guess would be Power Query.

Honestly it’s not hard - give me a minute… I’ll create a mock up for you.

6

Hi @michellepace,

Here’s the mock up file.
Due Date calculation.pbix (18.3 KB)

I hope this is helpful and let me know if you have any questions.

7

Thanks Melissa and Matteo. I’ll end up going with Melissa’s suggestion due to the efficiency of it (thanks for the sample file). However, for my own learning - - could you please tell me how I would write the code as written in the green comments?

c

I tried using Date.Add( ) but that doesn’t seem available. Then I tried the below, and then I get gap-a-dee-gaps. Why is this? What huge fundamental concept am I missing here. Errrg. I really am so confused. :frowning:

b
b821×443 128 KB

8

Hi @michellepace,

There’s a function called Date.EndOfMonth that will do just that.
If we nest these functions the full syntax will then become like below.

image
image980×179 9.95 KB

I’ve updated the sample file for your reference.
Due Date calculation.pbix (19.8 KB)

9

Use
SWITCH (
RELATED ( …,
30, EOMONTH( EDATE (DebtorPayables[MinDate], 1 ), 0 )
)

10

Thank you @AntrikshSharma , for a calculated column that works perfectly!
qqqqqqqqqq

it’s best to push this calculation as far back to the source as you can, in this case my guess would be Power Query.

@Melissa is this always the case? In other words, if I can do it in Power Query in place of a calculated column - then I should?

Not everything is ment to be solved with DAX

@Melissa, Again for my own learning here as I have your solution. Is it impossible to write this as DAX measure? And if yes, what made you know this right from the start?

11

Hi @michellepace,

There is so much content out there on DAX that easy to assume it’s the solution to everything…when instead you should always ask yourself the question: What am I trying to do and why?

In this case you are creating an attribute for analysis. Generally speaking attributes are created for slicing and dicing, they simplify the DAX needed to perform calculations and increase performance. So if something is static then more often than not I will try my hardest to get PQ to do it.

Pushing transformations as far back to the source, is just a best practice.
If the information is available at the source OR can be computed at the source, you should get it there. ETL transformations (in PQ) have an impact on datamodel refresh time but if that’s scheduled in the Service will mostly go by unnoticed.
A Calculated column can only be computed after all the data is loaded in the model, compression therefore can be lower, meaning it’s bigger in memory and that has a negative impact on performance.

So there’s a cost of having DAX doing a lot of the heavy lifting, be it performance, troubleshooting, maintenance, etc.
Just something to keep in mind, I guess.

.

Measures return scalar values and can’t be used as attributes for slicing and dicing. So the correct answer here is: it depends on how you would want to use that measure…

I hope this is helpful.

4 Likes
12

Melissa, your above reaply is extermely useful. Thank you very much.