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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
UlisesCiccola
Helper I
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
camargos88
Community Champion
Community Champion

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

 

Capture.PNG

 



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

Proud to be a Super User!



View solution in original post

1 REPLY 1
camargos88
Community Champion
Community Champion

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

 

Capture.PNG

 



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

Proud to be a Super User!



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors