Calculate difference value between cells in same column

UlisesCiccola — Thu, 19 Nov 2020 18:54:44 GMT

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 21.7 0

26/6/2011 21.7 0

27/6/2011 22.7 1

28/6/2011 23.7 1

29/6/2011 24.7 1

30/6/2011 25.7 1

1/11/2011 1 1

2/11/2011 2 1

3/11/2011 2.7 0.7

The first day of a month should be always 1 in the column difference. Is there a way to obtain this as a calculated column with dax (or either power query?) Thanks in advance!

Re: Calculate difference value between cells in same column

camargos88 — Thu, 19 Nov 2020 20:43:10 GMT

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

topic Calculate difference value between cells in same column in DAX Commands and Tips

Calculate difference value between cells in same column

Re: Calculate difference value between cells in same column