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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
sraj
Responsive Resident
Responsive Resident

Measure to substract value within a column

 

Hi there,

Is there anyway I can use a measure to calculate values in the same column?  I have the column from date, to date and payment, need a measure to calculate the increase value as shown below.  Please advise.

 

sraj_1-1645116718856.png

 

 

1 ACCEPTED SOLUTION

Hi, @sraj 

 

There's only one bug with your formula:

vjaneygmsft_0-1645527009626.png

Are these two columns the same? If it doesn't work, your actual data may not satisfy the logic in the code,

I have a simple method, you need to add a index column in power query first.

Then create a measure like this:

Inrease =
VAR a =
    MAXX (
        FILTER (
            ALL ( kro_EMP_Payment ),
            [Index]
                = SELECTEDVALUE ( kro_EMP_Payment[Index] ) - 1
        ),
        [PAYMENT]
    )
RETURN
    IF ( a <> BLANK (), SELECTEDVALUE ( kro_EMP_Payment[PAYMENT] ) - a )

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

 

Best Regards,

Community Support Team _Janey

 

View solution in original post

4 REPLIES 4
sraj
Responsive Resident
Responsive Resident

Got it, thank you very much!!

selimovd
Super User
Super User

Hey @sraj ,

 

you can just consider the rows where the TO date is a day earlier than the FROM date.

An approach like this should work as a calculated column:

Inrease = 
VAR vRowFromDayBefore = myTable[FROM] - 1
VAR vRowPayment = myTable[PAYMENT]
VAR vRowPaymnetDayBefore =
    CALCULATE (
        MAX ( myTable[PAYMENT] ),
        myTable[TO] = vRowFromDayBefore,
        ALL ( myTable )
    )
RETURN
    IF (
        vRowPaymnetDayBefore <> 0 && vRowPaymnetDayBefore <> BLANK (),
        vRowPayment - vRowPaymnetDayBefore
    )

 

The result looks for me like this:

selimovd_1-1645119673131.png

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

 

sraj
Responsive Resident
Responsive Resident

I dont know why I am seeing this, not sure what I am doing wrong?

 

Inrease =
VAR vRowFromDayBefore = kro_EMP_Payment[From] - 1
VAR vRowPayment = kro_EMP_Payment[payment]
VAR vRowPaymnetDayBefore =
CALCULATE (
MAX ( kro_EMP_Payment[Payment] ),
kro_EMP_Payment[To] = vRowFromDayBefore,
ALL ( kro_EMP_Payment )
)
RETURN
IF (
vRowPaymnetDayBefore <> 0 && vRowPaymnetDayBefore <> BLANK (),
vRowPayment - vRowPaymnetDayBefore
)

sraj_0-1645121586342.png

 

Hi, @sraj 

 

There's only one bug with your formula:

vjaneygmsft_0-1645527009626.png

Are these two columns the same? If it doesn't work, your actual data may not satisfy the logic in the code,

I have a simple method, you need to add a index column in power query first.

Then create a measure like this:

Inrease =
VAR a =
    MAXX (
        FILTER (
            ALL ( kro_EMP_Payment ),
            [Index]
                = SELECTEDVALUE ( kro_EMP_Payment[Index] ) - 1
        ),
        [PAYMENT]
    )
RETURN
    IF ( a <> BLANK (), SELECTEDVALUE ( kro_EMP_Payment[PAYMENT] ) - a )

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

 

Best Regards,

Community Support Team _Janey

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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