Measure to substract value within a column

[sraj]

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]

Got it, thank you very much!!

[selimovd]

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:

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]

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

)

[v-janeyg-msft · Accepted solution]

Hi, @sraj 

There's only one bug with your formula:

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