cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Naveennegi119 Member

## Increase and decrease status

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
RahulYadav Regular Visitor

## Re: Increase and decrease status

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

## 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" )
)
```
Naveennegi119 Member

## Re: Increase and decrease status

Thanks for such a fast reply.

Best Regards,

NICK

RahulYadav Regular Visitor

## Re: Increase and decrease status

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 Member

## Re: Increase and decrease status

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

Naveennegi119 Member

## Re: Increase and decrease status

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 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" )
)
```
Naveennegi119 Member

## Re: Increase and decrease status

That's work fine for me.

Thanks for giving ur time.

I will try ur solution some other data.

Best regards,

NICK