Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |