Datediff Formula question

[Anonymous]

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

[Anonymous]

@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 · Accepted solution]

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]

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

[v-henryk-mstf]

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.

[Anonymous]

@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]

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.

[sevenhills]

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]

@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]

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]

@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]

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]

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