Forum Discussion

sraj's avatar
sraj
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

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.

 

 

 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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

     

4 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    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's avatar
      sraj
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

      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's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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