cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
RahulYadav Regular Visitor
Regular Visitor

Re: Increase and decrease status

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" )
    )

 

2018-10-31_17-02-36.png

 

Thanks,

Rahul

Highlighted
Super User
Super User

Re: Increase and decrease status

@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 7
Super User
Super User

Re: Increase and decrease status

@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" )
    )

Re: Increase and decrease status

Hi @Zubair_Muhammad,

 

Thanks for such a fast reply.

 

Best Regards,

NICK

RahulYadav Regular Visitor
Regular Visitor

Re: Increase and decrease status

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" )
    )

 

2018-10-31_17-02-36.png

 

Thanks,

Rahul

Re: Increase and decrease status

HI @RahulYadav@Zubair_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

Re: Increase and decrease status

Hi @RahulYadav@Zubair_Muhammad

Previous data is wrong

 

work this data

IDValueincrease / decreaseHow to do it in powerbiFormula use in Excel
Nick134   
Nick124decrease?IF(B3>=B2,"increase","decrease")
Nick109decrease?IF(B4>=B3,"increase","decrease")
Zubair128   
Zubair113decrease?IF(B6>=B5,"increase","decrease")
Zubair135increase?IF(B7>=B6,"increase","decrease")
Rahul135   
Rahul110decrease?IF(B9>=B8,"increase","decrease")
Rahul141increase?IF(B10>=B9,"increase","decrease")

 

Sorry from side

Highlighted
Super User
Super User

Re: Increase and decrease status

@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" )
    )

Re: Increase and decrease status

Hi @Zubair_Muhammad,

 

That's work fine for me.

 

Thanks for giving ur time.

 

@RahulYadav,

I will try ur solution some other data.

 

Best regards,

NICK