Forum Discussion
anne-sophie
5 years agoFrequent Visitor
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 1...
- 5 years ago
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) - 5 years ago
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)
camargos88
5 years agoCommunity Champion
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)