cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## 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     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!

1 ACCEPTED SOLUTION
Community Champion

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

Proud to be a Super User!

Community Champion

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

Proud to be a Super User!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors