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 current date to know the amount of days that have passed by without the ticket being resolved. I have also attached the excel version of the fomula that works amazingly well but I am having issues fully convering it to power bi. 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)) Excel formula that actially workes. =IF(AND($AE38768<=AV$6,$AF38768>AV$6),AV$6-$AE38768,"") Created DayResolved DayClosed DayCreated monthResolved monthClosed monthCreated to Resolved DurationOpen?Jan 19 Open AgeFeb 19 Open AgeMar 19 Open AgeApr 19 Open AgeMay 19 Open AgeJun 19 Open AgeJul 19 Open AgeAug 19 Open AgeSep 19 Open AgeOct 19 Open AgeNov 19 Open AgeDec 19 Open AgeJan 20 Open AgeFeb 20 Open AgeMar 20 Open AgeApr 20 Open AgeMay 20 Open AgeJun 20 Open Age01/08/19 Jan-2019 OpenY23518211214317320423526529632635738841744847850953907/23/19 Jul-2019 OpenY 8396910013016119222125228231334309/18/19 Sep-2019 OpenY 12437310413516419522525628610/17/19 Oct-2019 OpenY 14447510613516619622725711/05/19 Nov-2019 OpenY 25568711614717720823811/14/19 Nov-2019 OpenY 16477810713816819922901/03/20 Jan-2020 OpenY 28578811814917901/17/20 Jan-2020 OpenY 14437410413516501/27/20 Jan-2020 OpenY 433649412515502/05/20 Feb-2020 OpenY 24558511614602/05/20 Feb-2020 OpenY 24558511614602/11/20 Feb-2020 OpenY 18497911014003/05/20 Mar-2020 OpenY 26568711704/01/20 Apr-2020 OpenY 29609004/20/20 Apr-2020 OpenY 10417105/05/20 May-2020 OpenY 265606/10/20 Jun-2020 OpenY 2006/26/20 Jun-2020 OpenY 406/30/20 Jun-2020 OpenY 0

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 current date to know the amount of days that have passed by without the ticket being resolved. I have also attached the excel version of the fomula that works amazingly well but I am having issues fully convering it to power bi.

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

Excel formula that actially workes.

=IF(AND($AE38768<=AV$6,$AF38768>AV$6),AV$6-$AE38768,"")

Created Day	Resolved Day	Closed Day	Created month	Resolved month	Closed month	Created to Resolved Duration	Open?	Jan 19 Open Age	Feb 19 Open Age	Mar 19 Open Age	Apr 19 Open Age	May 19 Open Age	Jun 19 Open Age	Jul 19 Open Age	Aug 19 Open Age	Sep 19 Open Age	Oct 19 Open Age	Nov 19 Open Age	Dec 19 Open Age	Jan 20 Open Age	Feb 20 Open Age	Mar 20 Open Age	Apr 20 Open Age	May 20 Open Age	Jun 20 Open Age
01/08/19			Jan-2019			Open	Y	23	51	82	112	143	173	204	235	265	296	326	357	388	417	448	478	509	539
07/23/19			Jul-2019			Open	Y							8	39	69	100	130	161	192	221	252	282	313	343
09/18/19			Sep-2019			Open	Y									12	43	73	104	135	164	195	225	256	286
10/17/19			Oct-2019			Open	Y										14	44	75	106	135	166	196	227	257
11/05/19			Nov-2019			Open	Y											25	56	87	116	147	177	208	238
11/14/19			Nov-2019			Open	Y											16	47	78	107	138	168	199	229
01/03/20			Jan-2020			Open	Y													28	57	88	118	149	179
01/17/20			Jan-2020			Open	Y													14	43	74	104	135	165
01/27/20			Jan-2020			Open	Y													4	33	64	94	125	155
02/05/20			Feb-2020			Open	Y														24	55	85	116	146
02/05/20			Feb-2020			Open	Y														24	55	85	116	146
02/11/20			Feb-2020			Open	Y														18	49	79	110	140
03/05/20			Mar-2020			Open	Y															26	56	87	117
04/01/20			Apr-2020			Open	Y																29	60	90
04/20/20			Apr-2020			Open	Y																10	41	71
05/05/20			May-2020			Open	Y																	26	56
06/10/20			Jun-2020			Open	Y																		20
06/26/20			Jun-2020			Open	Y																		4
06/30/20			Jun-2020			Open	Y																		0

Need Help

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

12 Replies

sevenhills
Super User
4 years ago
I updated the reply ... ignore this

Try replace and see if this work for you

'FreshService RAW DATA'[Created Date]

as

If( IsBlank('FreshService RAW DATA'[Created Date]), Today())
- 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
    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 ...
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.

Datediff Formula question.pbix73 KB
- 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
    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.
Anonymous
Not applicable
4 years ago
sevenhills

It working great but is there a way to restrict Resolved date to any date resolved date greater then 7/31/2020. What I was saying was I am having trouble adding this to the formula

'FreshService RAW DATA'[Resolved Date] >DATE(2020,7,31)

I was wondering if you had a suggestion on were to added it in the formula. When ever I try to add it I run into errors.
'FreshService RAW DATA'[Resolved Date] >DATE(2020,7,31)
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
  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) )

Forum Discussion

Datediff Formula question

12 Replies

Related content

DATEDIFF question

datediff dax -formula

DATEDIFF DAX

Bookmark questions

If formula