Forum Discussion
Solved
Multiple differences between values in the same column
I need to calculate the difference between dates in the same column. For example:
| Field | Input | Date |
| P1 | Enzow | 2024/01/24 |
| P1 | Plut | 2024/02/25 |
| P1 | Plut | 2024/02/19 |
| P1 | Plut | 2024/02/10 |
| P1 | Enzow | 2024/01/20 |
| P2 | Plut | 2024/02/10 |
| P2 | Enzow | 2024/01/20 |
| P2 | Enzow | 2024/01/12 |
| P2 | Enzow | 2024/01/10 |
I need to create a column that calculates the difference between the current date and the previous date in days, based on the Field and Input column. The final table would look something like this:
| Field | Input | Date | Difference |
| P1 | Enzow | 2024/01/24 | 4 |
| P1 | Plut | 2024/02/25 | 6 |
| P1 | Plut | 2024/02/19 | 9 |
| P1 | Plut | 2024/02/10 | |
| P1 | Enzow | 2024/01/20 | |
| P2 | Plut | 2024/02/10 | |
| P2 | Enzow | 2024/01/20 | 8 |
| P2 | Enzow | 2024/01/12 | 2 |
| P2 | Enzow | 2024/01/10 |
If there is no previous date, there should not be a value.
hello jobf
please check if this accomodate your need.
Difference =
var _MinDate = MINX(FILTER('Table','Table'[Field]=EARLIER('Table'[Field])&&'Table'[Input]=EARLIER('Table'[Input])),'Table'[Date])
var _PreviousDate =
CALCULATE(
MAX('Table'[Date]),
ALL('Table'),
OFFSET(-1,ORDERBY('Table'[Date]),PARTITIONBY('Table'[Field],'Table'[Input]))
)
Return
IF(
'Table'[Date]=_MinDate,
BLANK(),
'Table'[Date]-_PreviousDate
)Hope this will help you.
Thank you.
3 Replies
hello jobf
please check if this accomodate your need.
Difference =
var _MinDate = MINX(FILTER('Table','Table'[Field]=EARLIER('Table'[Field])&&'Table'[Input]=EARLIER('Table'[Input])),'Table'[Date])
var _PreviousDate =
CALCULATE(
MAX('Table'[Date]),
ALL('Table'),
OFFSET(-1,ORDERBY('Table'[Date]),PARTITIONBY('Table'[Field],'Table'[Input]))
)
Return
IF(
'Table'[Date]=_MinDate,
BLANK(),
'Table'[Date]-_PreviousDate
)Hope this will help you.
Thank you.
