Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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 !
@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)
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)
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |