Forum Discussion

amiller5's avatar
amiller5
Helper II
4 years ago
Solved

Date diff with an if statement?

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 da...
Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    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!

amiller5's avatar
amiller5
Helper II
4 years ago

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's avatar
grantsamborn
Solution Sage
4 years ago

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)