DATEADD formula not producing result for every row.

[Anonymous]

I have what I thought was a measure with a simple DATEADD formula and a simple conditional to put in a zero where a blank may exist.  It appears to be working for some but not all rows of data, where I have a non-blank value.  All the values are from the same table.  Approval_Date and Duration are facts.  I am clearly missing something basic in my coding logic or my understanding of how the DATEADD function works.  Here is the DAX Measure:

Close Date = 

VAR ZeroDuration = IF(ISBLANK(MAXX('FULL PROJECT LIST','FULL PROJECT LIST'[Duration])), 1, (MAXX('FULL PROJECT LIST','FULL PROJECT LIST'[Duration])))

RETURN

DATEADD('FULL PROJECT LIST'[Approval_Date], 7*ZeroDuration, DAY)

____________________

Here is what is showing up in the table (and I confirmed that the Measure was producing "1" for every row I had a blank value for Duration).  I green-checked a single example of each correct situation.  Why am I not getting a Close Date for every row with an Approval Date?  Why am I getting a Close Date for some rows with Duration values and not others?

 Thank you in advance for your help.

Mark

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download example PBIX file

DATEADD returns a table, not a single value, so you can't use it the way you are trying to

DATEADD function (DAX) - DAX | Microsoft Learn

I'm not following your arithemetic.  For example, are you adding 32 days duration to an Approval date of 28 Nov and getting a Close date of 10 July?

To fix this particular issue use this DAX instead

Close Date = 
VAR ZeroDuration = IF(ISBLANK(MAXX('FULL PROJECT LIST','FULL PROJECT LIST'[Duration])), 1, (MAXX('FULL PROJECT LIST','FULL PROJECT LIST'[Duration])))
RETURN
SELECTEDVALUE('Full Project List'[Approval Date]) + 7*ZeroDuration

Regards

Phil

[Anonymous]

Phil,

Worked great.  The only think I did differently was add an IF statement to "blank" the value if there is no Approval_Date to start with.

Thank you!

Mark

[Anonymous]

Phil,  Thank you for responding.   I will give that a try.   Just wanted to clarify that the duration is weeks...which is why I have the multiplier of 7 in there.  That is how that Nov 2022 date advances to Jul 2023.  I will add a comment to my DAX for clarification.

Thanks.

Mark