Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Datediff Formula question

Hi Guys,    I cant get this formula to work the way I want it.  This formula works perfectly but I also want it to caluclate If the resolved date is empty, I want it to calualte created date to cur...
Need Help
  • sevenhills's avatar
    sevenhills
    4 years ago

    I see you wanted to do is to calculate for those rows created date is before 7/31/2020 and resolved date is blank or after 7/31/2020.

     

    For the rows, you want to adjust the resolved date blank as the cut off date "7/31/2020".

     

     

     

    Tryout 2 Open Age = 
IF ( 
   -- only those rows with created date <= 7/31/2020 and resolved date as blank or after 7/31/2020
  ('FreshService RAW DATA'[Created Date] <= Date(2020,7,31) 
   && ( 
           IsBlank('FreshService RAW DATA'[Resolved Date]
           || 'FreshService RAW DATA'[Resolved Date] > Date(2020,7,31) 
       )
  ), 

  DATEDIFF(
   'FreshService RAW DATA'[Created Date], 

   IF (
       IsBlank('FreshService RAW DATA'[Resolved Date] ), 
       Date(2020,7,31), 
       'FreshService RAW DATA'[Resolved Date]
   )
  , DAY)
)

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

sevenhills 

 

What do you mean like this?  Mar 19 Open Age = If(Isblank('FreshService RAW DATA'[Created Date],Today())<=Date(2019,3,31),'FreshService RAW DATA'[Resolved Date]>Date(2019,3,31)),DATEDIFF('FreshService RAW DATA'[Created Date],DATE(2019,3,31),Day))

sevenhills's avatar
sevenhills
Super User
4 years ago

 

Mar 19 Open Age = 
IF ( 
  ('FreshService RAW DATA'[Created Date] <= Date(2019,3,31) 
   && IsBlank('FreshService RAW DATA'[Resolved Date]
  ), 

  DATEDIFF('FreshService RAW DATA'[Created Date], TODAY(), DAY),

  DATEDIFF('FreshService RAW DATA'[Created Date], 'FreshService RAW DATA'[Resolved Date], Day)
)

 

 

Change to your needs, added code for is blank chek and use Today in case of it ... 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    sevenhills 

     

    Thank you so much this works great. I have one question for you. Would it be possible to include this FreshService RAW DATA'[Resolved Date] > Date(2020,7,31)? I tried it but it keeps on giving me errors.

     

    IF('FreshService RAW DATA'[Created Date] <=DATE(2020,7,31) && ISBLANK('FreshService RAW DATA'[Resolved Date]),DATEDIFF('FreshService RAW DATA'[Created Date], DATE(2020,7,31), Day), DATEDIFF('FreshService RAW DATA'[Created Date],'FreshService RAW DATA'[Resolved Date],Day))
    • sevenhills's avatar
      sevenhills
      Super User
      4 years ago
      Tryout 2 Open Age = 
IF ( 
  ('FreshService RAW DATA'[Created Date] <= Date(2020,7,31) 
   && IsBlank('FreshService RAW DATA'[Resolved Date]
  ), 

  DATEDIFF('FreshService RAW DATA'[Created Date], Date(2020,7,31), DAY),

  DATEDIFF('FreshService RAW DATA'[Created Date], 'FreshService RAW DATA'[Resolved Date], Day)
)

       

      Yes, it should work, I dont see any issues with it. What error you are getting?

      • sevenhills's avatar
        sevenhills
        Super User
        4 years ago

        U mean you want to check for the resolved date as either blank or has to be > Date(2020,7,31) ?

         

        Try this and see

         

         

        Tryout 2 Open Age = 
IF ( 
  ('FreshService RAW DATA'[Created Date] <= Date(2020,7,31) 
   && ( 
           IsBlank('FreshService RAW DATA'[Resolved Date]
           || 'FreshService RAW DATA'[Resolved Date] > Date(2020,7,31) 
       )
  ), 

  DATEDIFF('FreshService RAW DATA'[Created Date], Date(2020,7,31), DAY),

  DATEDIFF('FreshService RAW DATA'[Created Date], 'FreshService RAW DATA'[Resolved Date], Day)
)