Forum Discussion

Naveennegi119's avatar
Naveennegi119
Helper III
7 years ago
Solved

Increase and decrease status

Hi all,

 

Data attached below

IDValueincrease / decreaseHow to do it in powerbiFormula use in Excel
ABC-1134   
ABC-2124decrease?IF(B3>=B2,"increase","decrease")
ABC-3109decrease?IF(B4>=B3,"increase","decrease")
MNO-1128   
MNO-2113decrease?IF(B6>=B5,"increase","decrease")
MNO-3135increase?IF(B7>=B6,"increase","decrease")
XYZ-1135   
XYZ-2110decrease?IF(B9>=B8,"increase","decrease")
XYZ-3141increase?IF(B10>=B9,"increase","decrease")

 

How to do in Power BI ?

 

 

Best regards,

NICK

 

 

Need Help
  • RahulYadav's avatar
    RahulYadav
    7 years ago

    Hi Naveennegi119,

    One more way to do it. 

     

    1. Add a Index column in Query Editor.

    2. Add Below formula in a Calculated column.

     

    Inc/Dec =
VAR PervValue =
    CALCULATE (
        MAX ( Table2[Value] ),
        FILTER ( Table2, Table2[Index] = EARLIER ( Table2[Index] ) - 1 )
    )
VAR prevID =
    CALCULATE (
        MAX ( Table2[ID] ),
        FILTER ( Table2, Table2[Index] = EARLIER ( Table2[Index] ) - 1 )
    )
VAR PerValueRev =
    IF ( LEFT ( Table2[ID], 3 ) <> LEFT ( Table2[prevID], 3 ), BLANK (), PervValue )
RETURN
    IF (
        PerValueRev = BLANK (),
        BLANK (),
        IF ( Table2[Value] > PerValueRev, "increase", "decrease" )
    )

     

     

    Thanks,

    Rahul

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Naveennegi119

     

    First add an index colum from Query Editor

    Then you can use this column

     

    Column =
VAR previousvalue =
    CALCULATE (
        MIN ( Table1[Value] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), [Index] = EARLIER ( [Index] ) - 1 )
    )
RETURN
    IF (
        previousvalue <> BLANK (),
        IF ( [Value] > previousvalue, "Increase", "decrease" )
    )

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Naveennegi119

     

    Try this calculated column

     

    Column =
VAR UniqueKey =
    LEFT ( [ID], 3 )
VAR myindex =
    VALUE ( RIGHT ( [ID], 1 ) )
VAR previousvalue =
    CALCULATE (
        MIN ( Table1[Value] ),
        FILTER (
            Table1,
            LEFT ( [ID], 3 ) = UniqueKey
                && VALUE ( RIGHT ( [ID], 1 ) )
                    = myindex - 1
        )
    )
RETURN
    IF (
        previousvalue <> BLANK (),
        IF ( [Value] > previousvalue, "Increase", "decrease" )
    )
  • RahulYadav's avatar
    RahulYadav
    Resolver II
    7 years ago

    Hi Naveennegi119,

    One more way to do it. 

     

    1. Add a Index column in Query Editor.

    2. Add Below formula in a Calculated column.

     

    Inc/Dec =
VAR PervValue =
    CALCULATE (
        MAX ( Table2[Value] ),
        FILTER ( Table2, Table2[Index] = EARLIER ( Table2[Index] ) - 1 )
    )
VAR prevID =
    CALCULATE (
        MAX ( Table2[ID] ),
        FILTER ( Table2, Table2[Index] = EARLIER ( Table2[Index] ) - 1 )
    )
VAR PerValueRev =
    IF ( LEFT ( Table2[ID], 3 ) <> LEFT ( Table2[prevID], 3 ), BLANK (), PervValue )
RETURN
    IF (
        PerValueRev = BLANK (),
        BLANK (),
        IF ( Table2[Value] > PerValueRev, "increase", "decrease" )
    )

     

     

    Thanks,

    Rahul

    • Naveennegi119's avatar
      Naveennegi119
      Helper III
      7 years ago

      HI RahulYadavZubair_Muhammad,

       

      It's very much complicated, I am changing(ID column) data

       

      IDValueincrease / decreaseHow to do it in powerbiFormula use in Excel
      Nick134   
      Zubair124decrease?IF(B3>=B2,"increase","decrease")
      Rahul109decrease?IF(B4>=B3,"increase","decrease")
      Nick128   
      Zubair113decrease?IF(B6>=B5,"increase","decrease")
      Rahul135increase?IF(B7>=B6,"increase","decrease")
      Nick135   
      Zubair110decrease?IF(B9>=B8,"increase","decrease")
      Rahul141increase?IF(B10>=B9,"increase","decrease")

       

      Help me for this situation

       

      Best Regards,

      NICK

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        Naveennegi119

         

        First add an index colum from Query Editor

        Then you can use this column

         

        Column =
VAR previousvalue =
    CALCULATE (
        MIN ( Table1[Value] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[ID] ), [Index] = EARLIER ( [Index] ) - 1 )
    )
RETURN
    IF (
        previousvalue <> BLANK (),
        IF ( [Value] > previousvalue, "Increase", "decrease" )
    )