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

v-henryk-mstf 

 

Your formula works great when there is a reolved date. The issue is when there is no resolved date. I want it to calculate from Created date to the date I input like 12/31/2021.  In excel I am easly able to that that but not power bi. 

 

This formula works great except for the last part. 

Mar 19 Open Age = If(AND('FreshService RAW DATA'[Created Date]<=Date(2019,3,31),'FreshService RAW DATA'[Resolved Date]>Date(2019,3,31)),DATEDIFF('FreshService RAW DATA'[Created Date],DATE(2019,3,31),Day))

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

Hi Anonymous ,

 

There are some calculation logic differences between powerbi and excel. In powerbi, each calculation column needs to be formed by creating a separate formula. It is not like excel where you can form multiple columns based on the same calculation logic.

If you want to display it in the way shown in the screenshot, you can only do so by creating separate calculated columns.


Best Regards,
Henry


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