Starting date and End Date same field - Diff in Dates

[romovaro]

Happy Friday

I have a question regarding difference in Days.

I have the table below With CLient Name, Entity Number, Status and Date.

THe column date is the Starting Date but also the End Date from the prev status.

Client Name	CUID	Questionnaire	Status	Date
Client A	1001487AU01	BluePrint Questionnaire	CLIENT REVIEW	10/11/2022 21:26:52
Client B	1001769IE01	BluePrint Questionnaire	CLIENT REVIEW	09/07/2022 11:35:25
Client C	1000970SG01	BluePrint Questionnaire	CLIENT REVIEW	08/29/2022 15:41:37
Client C	1000970SG01	BluePrint Questionnaire	UNDER REVIEW	09/15/2022 19:29:33
Client C	1000970SG01	BluePrint Questionnaire	WORKSHOP IN PROGRESS	09/16/2022 17:31:42
Client C	1000970SG01	BluePrint Questionnaire	CLIENT SIGNOFF IN PROGRESS	09/19/2022 09:20:09
Client C	1000970SG01	BluePrint Questionnaire	CLIENT SIGNED OFF	09/20/2022 18:19:45
Client D	1001505BE02	BluePrint Questionnaire	CLIENT REVIEW	08/02/2022 09:26:35
Client D	1001505BE02	BluePrint Questionnaire	UNDER REVIEW	09/19/2022 12:23:27
Client D	1001505BE02	BluePrint Questionnaire	WORKSHOP IN PROGRESS	09/19/2022 12:53:01
Client E	BioG1	BluePrint Questionnaire	CLIENT REVIEW	06/17/2022 11:05:07
Client E	BioG1	BluePrint Questionnaire	UNDER REVIEW	07/12/2022 15:31:25
Client E	BioG2	BluePrint Questionnaire	CLIENT REVIEW	07/26/2022 19:02:14
Client E	BioG2	BluePrint Questionnaire	UNDER REVIEW	07/26/2022 19:27:07
Client E	BioG1	BluePrint Questionnaire	WORKSHOP IN PROGRESS	07/27/2022 15:21:19
Client E	BioG1	BluePrint Questionnaire	CLIENT SIGNOFF IN PROGRESS	07/27/2022 16:36:47
Client E	BioG1	BluePrint Questionnaire	CLIENT SIGNED OFF	07/27/2022 16:49:41

Example Client C

Client Review starting 08/29/2022 and ends 09/15/2022

Under Review starting 09/15/2022 and ends 09/16/2022

etc.

I need to provide difference in days within Status

I have the formula below:

Diff_Days =

VAR temp =

TOPN (

1,

FILTER (

'BPQ Status',

'BPQ Status'[Client Name] = EARLIER ( 'BPQ Status'[Client Name] )

&& 'BPQ Status'[Date] < EARLIER ( 'BPQ Status'[Date] )

),

'BPQ Status'[Date], DESC

)

RETURN

DATEDIFF ( MINX ( temp, 'BPQ Status'[Date]), 'BPQ Status'[Date], DAY )

The formula shows the diff in days but shows the diff in the Wrong Status. IN the example below, Client Review takes 17 days but it's "under review" row.

The problem is other calculations are also affected (Average of days x status, etc.)

 

Any idea how to provide the Av days showing in the correct Status.

Thanks

@jgeddes 

[romovaro]

Hi Michael

I see that customers with only Status "Client Review" are not showing in the tables, etc.

Example CLient A and B

Any idea how to fix that?

Ideally should be from date to "today's date"

Thanks

@Mikelytics 

[Mikelytics]

Hi @romovaro 

Can you please try:

Datediff2 = 

var var_Client = [Client Name]
var var_StartDate = [Date]
var var_EndDate = 
   CALCULATE(
      MIN([Date]),
      FILTER(
         ALL('BPQ Status'),
         [Client Name] = var_Client &&
         [Date]>var_StartDate
      )
   )

RETURN
IF(
   NOT ISBLANK(var_EndDate),
   DATEDIFF(var_StartDate,var_EndDate,DAY),
   DATEDIFF(var_StartDate,TODAY(),DAY)
)

I only added the followin part

since I check upfront the next available date (var_EndDate) after the current date (var_StartDate) I can say that if there is no next date - NOT ISBLANK(var_EndDate) = False, I can use this as an indicator to calculate TODAY() - start date

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[romovaro]

THANKS 🙂

[Mikelytics]

Hi @romovaro 

If I understand correctly you use a calculated column right? Can you üplease try the following? 

Datediff = 

var var_Client = [Client Name]
var var_EndDate = [Date]
var var_StartDate = 
   CALCULATE(
      MAX([Date]),
      FILTER(
         ALL('BPQ Status'),
         [Client Name] = var_Client &&
         [Date]<var_EndDate
      )
   )

RETURN
IF(
   NOT ISBLANK(var_StartDate),
   DATEDIFF(var_StartDate,var_EndDate,DAY)
)

EDIT: @romovaro  1 minute after my post I made a change by replacing a comma with &&

Aditionally I tried the formula based on your dataset by my own (Please see below) and it seems to work

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[romovaro]

HI MIke

Thanks for your answer.

I see that I get the same results as my formula.

The issue with the formula (same with mine) is that, for example, if I want to show the difference in days for customer C & Client Review (How many days does it take the client to review the contract), it shows 0 days/Blank when in reality, it took 17 days.

Formulas are working ok but I would like to show the difference of days in the right row

THanks

[Mikelytics · Accepted solution]

Hi @romovaro 

ah okay, thoight this is what you wanted to have. I created a second one. Please see below:

Datediff2 = 

var var_Client = [Client Name]
var var_StartDate = [Date]
var var_EndDate = 
   CALCULATE(
      MIN([Date]),
      FILTER(
         ALL('BPQ Status'),
         [Client Name] = var_Client &&
         [Date]>var_StartDate
      )
   )

RETURN
IF(
   NOT ISBLANK(var_EndDate),
   DATEDIFF(var_StartDate,var_EndDate,DAY)
)

better?

@romovaro  EDIT: I found an error in my screenshot with negative values in datediff2. THis was because my datecolumn was formatted as text. After I formatted it to date it worked fine. Please find below the updated screenshot after updating the data type of my date coliumn. Formula is still the same

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn