Forum Discussion

anne-sophie's avatar
anne-sophie
Frequent Visitor
5 years ago
Solved

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...
  • camargos88's avatar
    5 years ago

    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)

     

  • amitchandak's avatar
    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)