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

 

 

 

 

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!

4 Replies

  • 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)
  • parry2k's avatar
    parry2k
    Super User
    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!