Why isn't this validating of the Date value working inside this Calculated Column?

[D_PBI]

Hi,
Take a look at the below screenshot. This contains the DAX for the 'Status Duration' calculated column. The resultset is shown in the table visual.
It would seem the __EndDateToUse variable isn't being calculated correctly as the 'Status Duration' is empty in the table and therefore it isn't using TODAY() as the second part of the DATEDIFF function, expected.
Why this is not working?
Whatever the issue is I expect it to be present for the __PreviousEndDatetoUse variable, should that condition be met.
Thanks in advance.

[v-rongtiep-msft · Accepted solution]

Hi @D_PBI ,

Please have a try.

Column = var _1=CALCULATE(MAX('Table'[end date]),FILTER('Table','Table'[Index]<EARLIER('Table'[Index])&&'Table'[number]=EARLIER('Table'[number])))
var _2=IF('Table'[end date]=BLANK(),TODAY(),'Table'[end date])
return
DATEDIFF(_1,_2,DAY)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[D_PBI]

Hi @v-rongtiep-msft - thank you for your post. I was able to complete the needed myself. Having checked your solution, it matches how I achieved it.

[D_PBI]

Bump.
To add, when I place the __EndDateToUse variable DAX inside a standalone calculated column it does show today's date, as expected. However, when I when I try and use the __EndDateToUse variable (or call the standalone calculated column) as the second parameter in the DATEDIFF function, it doesn't work - why?