Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anne-sophie
Frequent 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:

xProjectxValuexDateDifference
Project1100011/20/20201
Project199911/17/20200
Project199911/13/2020 
Project25411/20/20201
Project25311/17/2020-7
Project26011/13/2020 

Thanks in advance!

2 ACCEPTED SOLUTIONS
camargos88
Community Champion
Community Champion

@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)

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

amitchandak
Super User
Super User

@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)

View solution in original post

3 REPLIES 3
anne-sophie
Frequent Visitor

Thank you both for your help !

amitchandak
Super User
Super User

@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
Community Champion
Community Champion

@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)

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors