Forum Discussion
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 to get the date difference per status. Is it possible to calculate within same column? (Note: S - start date, E - end date)
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
2 Replies
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