Increase and decrease status

[Naveennegi119]

Hi all,

Data attached below

ID	Value	increase / decrease	How to do it in powerbi	Formula use in Excel
ABC-1	134
ABC-2	124	decrease	?	IF(B3>=B2,"increase","decrease")
ABC-3	109	decrease	?	IF(B4>=B3,"increase","decrease")
MNO-1	128
MNO-2	113	decrease	?	IF(B6>=B5,"increase","decrease")
MNO-3	135	increase	?	IF(B7>=B6,"increase","decrease")
XYZ-1	135
XYZ-2	110	decrease	?	IF(B9>=B8,"increase","decrease")
XYZ-3	141	increase	?	IF(B10>=B9,"increase","decrease")

How to do in Power BI ?

Best regards,

NICK

[RahulYadav · Accepted solution]

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

[RahulYadav · Accepted solution]

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]

HI @RahulYadav@Zubair_Muhammad,

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

ID	Value	increase / decrease	How to do it in powerbi	Formula use in Excel
Nick	134
Zubair	124	decrease	?	IF(B3>=B2,"increase","decrease")
Rahul	109	decrease	?	IF(B4>=B3,"increase","decrease")
Nick	128
Zubair	113	decrease	?	IF(B6>=B5,"increase","decrease")
Rahul	135	increase	?	IF(B7>=B6,"increase","decrease")
Nick	135
Zubair	110	decrease	?	IF(B9>=B8,"increase","decrease")
Rahul	141	increase	?	IF(B10>=B9,"increase","decrease")

Help me for this situation

Best Regards,

NICK

[Zubair_Muhammad · Accepted solution]

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

[Naveennegi119]

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

[Naveennegi119]

Hi @RahulYadav@Zubair_Muhammad

Previous data is wrong

work this data

ID	Value	increase / decrease	How to do it in powerbi	Formula use in Excel
Nick	134
Nick	124	decrease	?	IF(B3>=B2,"increase","decrease")
Nick	109	decrease	?	IF(B4>=B3,"increase","decrease")
Zubair	128
Zubair	113	decrease	?	IF(B6>=B5,"increase","decrease")
Zubair	135	increase	?	IF(B7>=B6,"increase","decrease")
Rahul	135
Rahul	110	decrease	?	IF(B9>=B8,"increase","decrease")
Rahul	141	increase	?	IF(B10>=B9,"increase","decrease")

Sorry from side

[Zubair_Muhammad]

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

[Naveennegi119]

Hi @Zubair_Muhammad,

Thanks for such a fast reply.

Best Regards,

NICK