Date diff with an if statement?

[amiller5]

Hello - 

I am trying to create a Turn around time calculation using a date for opened and closed. When the file has nothing in the date closed column, I want the calculation to be against another date in a key table I have. Is that possible to do with date diff? See screen shot of data and current calculated column for date diff - but note when there is not closed date, it remains blank.

Different table has the refresh month date as a date key

[parry2k · Accepted solution]

@amiller5 try this:

Number of Months = 
DATEDIFF( 
    'Table'[Open Date Column],
    COALESCE ( 
           'Table'[Closed Date Column], 
           CALCULATE ( MAX ( 'Key Date Table'[Key Date Column] ) 
    ),
    MONTH
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[gauthamboppana]

@amiller5  - Yes, it is possible. Is there a relationship between these two date columns? The sample syntax is like below.

Measure = 
Switch(TRUE(),
Table[Date1] = "",  DATEDIFF('Table'[StartDate],'Table'[End Date1],MONTH),
 DATEDIFF('Table'[StartDate],'Table'[End Date2],MONTH),
)

[amiller5]

No - the date key and the service 1 table do not have a relationship. The date key table is an input table for me to calculate other measures on. We use this specifically for looking at KPIs but retrospecitvely - so we are grabbing updated data in July to assess June's measures.

[grantsamborn]

Maybe give this a try.

Test =

VAR _End =

IF(

    SELECTEDVALUE('Table1'[Date/Time Closed]) = BLANK(),

    EOMONTH(MAX('OtherTable'[Date]), 0),

    SELECTEDVALUE('Table1'[Date/Time Closed])

)

RETURN

    DATEDIFF(SELECTEDVALUE('Table1'[Date/Time Opened]), _End, DAY)