Forum Discussion
Solved
Rolling 5 row Difference
Hello, I am new to Power BI and cannot figure out how to create a column that would be simple to create in Excel. I would like to create a new column that takes the difference between every 5 ro...
Hi,
Sorry that I cannot know the logic of the order in the Date Column. So, I created a index column that shows the order of each row.
Please check the below picture and the attached pbix file.
Expected result CC =VAR currentrow = Data[Index]VAR currentvalue = Data[C3A3 ICE Index]VAR previousvalue =CALCULATE (MAX ( Data[C3A3 ICE Index] ),FILTER ( Data, Data[Index] = currentrow - 4 ))RETURNIF ( previousvalue <> BLANK (), currentvalue - previousvalue )Gain or Loss CC =IF (Data[Expected result CC] > 0,"Gain",IF ( Data[Expected result CC] < 0, "Loss" ))
Jihwan_Kim
Super User
Super UserHi,
Sorry that I cannot know the logic of the order in the Date Column. So, I created a index column that shows the order of each row.
Please check the below picture and the attached pbix file.
Expected result CC =
VAR currentrow = Data[Index]
VAR currentvalue = Data[C3A3 ICE Index]
VAR previousvalue =
CALCULATE (
MAX ( Data[C3A3 ICE Index] ),
FILTER ( Data, Data[Index] = currentrow - 4 )
)
RETURN
IF ( previousvalue <> BLANK (), currentvalue - previousvalue )
Gain or Loss CC =
IF (
Data[Expected result CC] > 0,
"Gain",
IF ( Data[Expected result CC] < 0, "Loss" )
)
This worked! THANK YOU! I needed to create some visuals for a meeting with my boss tomorrow and I can now create the correct ones. Really appreciate the help 🙂
