Forum Discussion

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive 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 D...
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

     

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive 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
    Icon for Resident Rockstar rankResident 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