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!

gauthamboppana's avatar
gauthamboppana
Solution Specialist
4 years ago

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'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)