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

     

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