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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Ibrahim_shaik
Helper V
Helper V

Need Help in Creating a Column/measure in a Table

Hello Power BI Community,

 

I want to create a column/measure which has subtracted values from the other column in a table.

 

I have a table with date, time, number1, number2 columns. I want to know the difference between two values in number1 column for example m1, m2, m3 are the values, I want to know the difference between m2-m1, m3-m2 like this for the all the values in the column and for the rest of the columns as well.

 

Note: Here I want to Subtract Values within the Column not from a adjacent column.

 

Please give a solution to this. Should i create a calculated column or measure?

 

Please suggest.

 

Thanks&Regards,

Ibrahim

2 ACCEPTED SOLUTIONS
Dangar332
Super User
Super User

Hi, @Ibrahim_shaik 
@Ibrahim_shaik  use below 

use below code for new column

 

 

new = 
var a = 'Table'[number1]
var b = CALCULATE(
            MIN('Table'[number1]),
            OFFSET(1,ORDERBY('Table'[date],asc,'Table'[time],ASC)),
            ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0
RETURN
b-a

 

 

Dangar332_0-1709712165707.png

 

and for measure use below code

 

Measure = 
var a = MIN('Table'[number1])
var b = CALCULATE(
           MIN('Table'[number1]),
           OFFSET(1,ALL('Table'[date],'Table'[time]),
           ORDERBY(MIN('Table'[date]),ASC,MIN('Table'[time]),ASC)),
           ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0
RETURN
b-a

 

 

Dangar332_1-1709712207300.png

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

hi, @Ibrahim_shaik 

means you want to replace -25 with 0 and 
if yes then use below column code 

 

new = 
var a = 'Table'[number1]
var b = CALCULATE(
            MIN('Table'[number1]),
            OFFSET(1,ORDERBY('Table'[date],asc,'Table'[time],ASC)),
            ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0

RETURN
IF(b-a<0,0,b-a)

 

it replace negative value wwith zero(0)

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

11 REPLIES 11
Ibrahim_shaik
Helper V
Helper V

Hi @Dangar332 ,

 

the calculated column code give the last column as it is in the subtracted column.

I want the subtracted value but it takes last value same in the subtracted column what should I change in the code?

 

Calculated column.png

 

And the last value is adding up in the summation which is not correct.

hi, @Ibrahim_shaik 

 

you need substracted column sum in total?
or something else can you elaborate 

Hi @Dangar332 ,

 

I need a Subracted Column and the Subtracted column values SUM.

 

I have added ABS(b-a) in the DAX to get a Positive Value as I need sum of the subtracted values

hi, @Ibrahim_shaik 

i think for that you need to use measure code
use below measure code

 

Measure = 
var a = MIN('Table'[number1])
var b = CALCULATE(
           MIN('Table'[number1]),
           OFFSET(1,ALL('Table'[date],'Table'[time]),
           ORDERBY(MIN('Table'[date]),ASC,MIN('Table'[time]),ASC)),
           ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0
var c= SUMX('Table', 
            var a = 'Table'[number1]
            var b =CALCULATE(
                       MIN('Table'[number1]),
                       OFFSET(1,ALL('Table'[date],'Table'[time]),
                       ORDERBY(MIN('Table'[date]),ASC,MIN('Table'[time]),ASC)),
                       ALLEXCEPT('Table','Table'[date],'Table'[time])
                    )+0
            RETURN
            abs(b-a)
        )    
RETURN
IF(ISINSCOPE('Table'[date]),b-a,c)

 

 

see below image

Dangar332_0-1709806951909.png

 

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

Dangar332
Super User
Super User

Hi, @Ibrahim_shaik 
@Ibrahim_shaik  use below 

use below code for new column

 

 

new = 
var a = 'Table'[number1]
var b = CALCULATE(
            MIN('Table'[number1]),
            OFFSET(1,ORDERBY('Table'[date],asc,'Table'[time],ASC)),
            ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0
RETURN
b-a

 

 

Dangar332_0-1709712165707.png

 

and for measure use below code

 

Measure = 
var a = MIN('Table'[number1])
var b = CALCULATE(
           MIN('Table'[number1]),
           OFFSET(1,ALL('Table'[date],'Table'[time]),
           ORDERBY(MIN('Table'[date]),ASC,MIN('Table'[time]),ASC)),
           ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0
RETURN
b-a

 

 

Dangar332_1-1709712207300.png

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Here in the measure column what is happening is the measuring is adding all the "4" 5 integers and subtracting with the last value -25 and the result is ABS(20-25) = 5 but I don't want to subtract the sum of upper values with the last value. that is not the correct summation right.

 

I understand the last value is showing the same value as there are no other values below to subtract with and show the actual value, so it is showing as -25. But for that can we put a condition if there are no other values below to subtract just show as 0.

 

And Continue to subtract when there are new values below to subtract when the new data comes in the table.

hi, @Ibrahim_shaik 

means you want to replace -25 with 0 and 
if yes then use below column code 

 

new = 
var a = 'Table'[number1]
var b = CALCULATE(
            MIN('Table'[number1]),
            OFFSET(1,ORDERBY('Table'[date],asc,'Table'[time],ASC)),
            ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0

RETURN
IF(b-a<0,0,b-a)

 

it replace negative value wwith zero(0)

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Everything is fine with the DAX but the issue comes when the subtraction happens between lower value - higher value.

 

Example: 43-53 = -10

with the DAX what it does is it will give the value as 0 so in the IF condition I have changed it to if (b-a<0, ABS(b-a), b-a).

 

But the Issue with this is in the last value of the column as it has no other value below to subtract so it is in - negative and considering it as + positive and the same value is coming into the subtracted column value and its getting added up in the summation of the subtracted column.

 

Basically I don't want to consider the last value of the column in the summation.

 

Can I Put a condition with the Index column where it takes the summation of n-1 column? 

hi, @Ibrahim_shaik 

as i know it not posiible in column 

so use below measure

Measure = var a = MIN('Table'[number1])
var b = CALCULATE(
           MIN('Table'[number1]),
           OFFSET(1,ALL('Table'[date],'Table'[time]),
           ORDERBY(MIN('Table'[date]),ASC,MIN('Table'[time]),ASC)),
           ALLEXCEPT('Table','Table'[date],'Table'[time])
        )+0
var c= SUMX('Table', 
            var a = 'Table'[number1]
            var b =CALCULATE(
                       MIN('Table'[number1]),
                       OFFSET(1,ALL('Table'[date],'Table'[time]),
                       ORDERBY(MIN('Table'[date]),ASC,MIN('Table'[time]),ASC)),
                       ALLEXCEPT('Table','Table'[date],'Table'[time])
                    )+0
            RETURN
            IF(b-a<0,0,b-a)
        )    
var d = IF(ISINSCOPE('Table'[date]),b-a,c)
return
d

 

Dangar332_0-1710056710971.png

 

Hi @Dangar332 ,

 

This Measure is Perfect.

 

But I have three Columns for which I have to do this calculation and I cannot use measure to calculate for three columns the visual will not take three measures.

 

It shows the "Visual resources are Exceeded".

 

I only have to Use Calculated Column to calculate three columns.

 

Measure is good to calcluate the difference for 1 column but as you know I have three columns HVAC, Other, Total.

 

how to use calculated column for the same result.

Hi @Dangar332 ,

 

Thank you so much for the quick response.

 

I thought of creating an another calculated column for index and then with the help of that I'll do the subtraction within the column but the DAX code you have shared doesn't require any other columns it's very interesting and I learned from this.

 

I have used the DAX for the Calculated Column and it works fine.

 

Thanks alot.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.