Forum Discussion

UlisesCiccola's avatar
UlisesCiccola
Helper I
5 years ago
Solved

Calculate difference value between cells in same column

Hello everyone,   I am quite new to DAX and need to obtain a calculated column (named difference) based on the following data:    Date               Value             Difference   25/6/2011    ...
Calculated columns
DAX
Power Query
  • camargos88's avatar
    camargos88
    5 years ago

    UlisesCiccola ,

     

    Try this code for a new calculated column:

     

    Difference = 
VAR _date = CALCULATE(MAX('Table'[Date]), FILTER('Table', 'Table'[Date] < EARLIER('Table'[Date])))
VAR _lastValue =  CALCULATE(MAX('Table'[Value]), FILTER('Table', 'Table'[Date] = _date))
RETURN  
    IF(
        _lastValue = BLANK(), 0,
         IF(DAY('Table'[Date]) = 1, 1, 'Table'[Value] - _lastValue)
    )

     

     

camargos88's avatar
camargos88
Community Champion
5 years ago

UlisesCiccola ,

 

Try this code for a new calculated column:

 

Difference = 
VAR _date = CALCULATE(MAX('Table'[Date]), FILTER('Table', 'Table'[Date] < EARLIER('Table'[Date])))
VAR _lastValue =  CALCULATE(MAX('Table'[Value]), FILTER('Table', 'Table'[Date] = _date))
RETURN  
    IF(
        _lastValue = BLANK(), 0,
         IF(DAY('Table'[Date]) = 1, 1, 'Table'[Value] - _lastValue)
    )