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 

 

I reward the fomula to its simplest form,  Can this be done below in powe bi? I did it but got an error message. 

 

I wanted to calculate the date difference between CREATED DATE that’s Less than or equal to (<=) 7/31/2020 and RESOLVED DATE that’s higher then (>) 7/31/2020. But if RESOLVED DATE is blank Then calculate date difference between 7/31/2020 and CREATED DATE.

 

Tesst 4 = DATEDIFF('FreshService RAW DATA'[Created Date] <=DATE(2020,7,31), 'FreshService RAW DATA'[Resolved Date] >DATE(2020,7,31), Day) &&ISBLANK('FreshService RAW DATA'[Resolved Date]), DATEDIFF(Date(2020,7,31), 'FreshService RAW DATA'[Created Date))

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