Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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" ) )
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
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
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" ) )
Hi @Zubair_Muhammad,
That's work fine for me.
Thanks for giving ur time.
I will try ur solution some other data.
Best regards,
NICK
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
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" ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |