Date Difference in Measure

[Vaibhavi195]

IF(
isnull([Closed.autoCalendar.Date]),
(now()-[Closed.autoCalendar.Date],'DD'),
([Opened.autoCalendar.Date]-[Closed.autoCalendar.Date],'DD')
)

giving error

[Oleg_Troyansky]

the problem is in the ,'DD' part - you should remove that:

IF(
isnull([Closed.autoCalendar.Date]),
(now()-[Closed.autoCalendar.Date],'DD'),
([Opened.autoCalendar.Date]-[Closed.autoCalendar.Date],'DD')
)

But also, the function now() returns a complete timestamp. so I'd rather use today(), or add the function Floor() on top of it. 

Also, I believe you used the Closed date instead of the Opened date in the first part of the formula. When the closed date is null, you don't want to use it in the calculation, right?

Look into the function Alt() - it can help you simplify your formula and eliminate the IF() function.

Cheers,

[cristianj23a]

Hello I recommend these codes:

In Scripting:

Data:
LOAD * INLINE [
Opened.autoCalendar.Date, Closed.autoCalendar.Date
2023-08-01, 2023-08-05
2023-08-02, 2023-08-06
2023-08-03, 2023-08-07
];

Result:
LOAD *,
IF(
IsNull(Closed.autoCalendar.Date),
Date(Today()) - Date(Closed.autoCalendar.Date),
Date(Opened.autoCalendar.Date) - Date(Closed.autoCalendar.Date)
) AS DateDifferenceInDays
RESIDENT Data;
Drop Table Data;

In Set Analisys Measure:

IF(
IsNull(Closed.autoCalendar.Date),
Date(Today()) - Date(Closed.autoCalendar.Date),
Date(Opened.autoCalendar.Date) - Date(Closed.autoCalendar.Date)
)

Regarts.