Forum Discussion
Solved
Calculate difference between 2 dates
Hello,
I'd like to calculate the difference between 2 values within the same table, with 2 conditions: same xProject, previous xDate.
Example:
| xProject | xValue | xDate | Difference |
| Project1 | 1000 | 11/20/2020 | 1 |
| Project1 | 999 | 11/17/2020 | 0 |
| Project1 | 999 | 11/13/2020 | |
| Project2 | 54 | 11/20/2020 | 1 |
| Project2 | 53 | 11/17/2020 | -7 |
| Project2 | 60 | 11/13/2020 |
Thanks in advance!
Try this code for a calculated column:
Difference = VAR _previousDate = CALCULATE(MAX('Table'[xDate]), FILTER('Table', 'Table'[xProject] = EARLIER('Table'[xProject]) && 'Table'[xDate] < EARLIER('Table'[xDate]))) VAR _previousValue = CALCULATE(MAX('Table'[xValue]), FILTER('Table', 'Table'[xProject] = EARLIER('Table'[xProject]) && [xDate] = _previousDate)) RETURN IF(_previousValue = BLANK(), BLANK(), 'Table'[xValue] - _previousValue)This for measure:
_Difference = VAR _previousDate = CALCULATE(MAX('Table'[xDate]), FILTER(ALLEXCEPT('Table', 'Table'[xProject]), 'Table'[xDate] < SELECTEDVALUE('Table'[xDate]))) VAR _previousValue = CALCULATE(MAX('Table'[xValue]), FILTER(ALLEXCEPT('Table', 'Table'[xProject]), [xDate] = _previousDate)) RETURN IF(_previousValue = BLANK(), BLANK(), SELECTEDVALUE('Table'[xValue]) - _previousValue)anne-sophie , Try a new column like
Column = var _1= maxx(filter('Table', [xProject] =EARLIER([xProject]) && [xDate] <EARLIER([xDate])),[xDate]) var _2 =maxx(filter('Table', [xProject] =EARLIER([xProject]) && [xDate] =_1 ),[xValue]) return if(ISBLANK(_1),blank() , [xValue]-_2)
3 Replies
Try this code for a calculated column:
Difference = VAR _previousDate = CALCULATE(MAX('Table'[xDate]), FILTER('Table', 'Table'[xProject] = EARLIER('Table'[xProject]) && 'Table'[xDate] < EARLIER('Table'[xDate]))) VAR _previousValue = CALCULATE(MAX('Table'[xValue]), FILTER('Table', 'Table'[xProject] = EARLIER('Table'[xProject]) && [xDate] = _previousDate)) RETURN IF(_previousValue = BLANK(), BLANK(), 'Table'[xValue] - _previousValue)This for measure:
_Difference = VAR _previousDate = CALCULATE(MAX('Table'[xDate]), FILTER(ALLEXCEPT('Table', 'Table'[xProject]), 'Table'[xDate] < SELECTEDVALUE('Table'[xDate]))) VAR _previousValue = CALCULATE(MAX('Table'[xValue]), FILTER(ALLEXCEPT('Table', 'Table'[xProject]), [xDate] = _previousDate)) RETURN IF(_previousValue = BLANK(), BLANK(), SELECTEDVALUE('Table'[xValue]) - _previousValue)anne-sophie , Try a new column like
Column = var _1= maxx(filter('Table', [xProject] =EARLIER([xProject]) && [xDate] <EARLIER([xDate])),[xDate]) var _2 =maxx(filter('Table', [xProject] =EARLIER([xProject]) && [xDate] =_1 ),[xValue]) return if(ISBLANK(_1),blank() , [xValue]-_2)Thank you both for your help !
