Calculate difference between 2 dates

[anne-sophie]

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!

[camargos88 · Accepted solution]

@anne-sophie ,

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]

Thank you both for your help !

[amitchandak · Accepted solution]

@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 · Accepted solution]

@anne-sophie ,

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)