Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to calculate date difference within same column

Hi everyone,   Good afternoon! I would like to ask how to get the date difference of the start and end date in one column (the same column). I have a different status for each track ID, but I need...
Need Help
Show and Tell
Tips and Tricks
  • AntonioM's avatar
    AntonioM
    4 years ago

    Hi, If you want to add a new column, I would try

     

    StartToEnd =

VAR startdate = CALCULATE(MIN(Table[Signoff Date]),FILTER(Table, Table[Change Number] = EARLIER(Table[Change Number]) && Table[Status] = EARLIER(Table[Status])))

VAR enddate = CALCULATE(MAX(Table[Signoff Date]),FILTER(Table, Table[Change Number] = EARLIER(Table[Change Number]) && Table[Status] = EARLIER(Table[Status])))

RETURN DATEDIFF(startdate, enddate, DAY)

     

    That should give you a duration for each combination of Change Number and Status

AntonioM's avatar
AntonioM
Solution Sage
4 years ago

Hi, If you want to add a new column, I would try

 

StartToEnd =

VAR startdate = CALCULATE(MIN(Table[Signoff Date]),FILTER(Table, Table[Change Number] = EARLIER(Table[Change Number]) && Table[Status] = EARLIER(Table[Status])))

VAR enddate = CALCULATE(MAX(Table[Signoff Date]),FILTER(Table, Table[Change Number] = EARLIER(Table[Change Number]) && Table[Status] = EARLIER(Table[Status])))

RETURN DATEDIFF(startdate, enddate, DAY)

 

That should give you a duration for each combination of Change Number and Status

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi! Thanks a lot for this! It worked perfectly. To add, could you also help me how can I remove weekends in the calculation? Appreciate your help.