Forum Discussion

romovaro's avatar
romovaro
Responsive Resident
3 years ago
Solved

Starting date and End Date same field - Diff in Dates

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 

 

Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    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

     

6 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    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's avatar
      romovaro
      Responsive Resident
      3 years ago

      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's avatar
        Mikelytics
        Resident Rockstar
        3 years ago

        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

         

  • romovaro's avatar
    romovaro
    Responsive Resident
    3 years ago

    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's avatar
      Mikelytics
      Resident Rockstar
      3 years ago

      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