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

     

     

v-henryk-mstf's avatar
v-henryk-mstf
Community Support
4 years ago

Hi Anonymous ,

 

According to your description, I did a test reference as follows:

 

Colopen_age = 
IF (
    'FreshService RAW DATA'[Created Day] <= DATE ( 2019, 10, 31 )
        && 'FreshService RAW DATA'[Resolved Day] >= DATE ( 2019, 10, 31 ),
    DATEDIFF ( 'FreshService RAW DATA'[Created Day], DATE ( 2019, 10, 31 ), DAY )
)

 


If the problem is still not resolved, please point it out. Looking forward to your feedback.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.