Rolling 5 row Difference

[kb856]

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 rows for C3A3 ICE Index (shown below). So, it would take the value from May 9, 2017 minus September 8, 2014, then June 16, 2017 - September 11, 2014, and so on. I realize that this would mean there is a blank value in the first 5 rows, since there is not another value to subtract from above them.

I would then like to create another column to show "Loss" if the number calculated before is negative (meaning the value decreased over time), and "Gain" if the number is positive (meaning the value increased over time).

I will be forever thankful if someone can help. Thanks!

[Jihwan_Kim · Accepted solution]

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 )

)

RETURN

IF ( previousvalue <> BLANK (), currentvalue - previousvalue )

Gain or Loss CC =

IF (

Data[Expected result CC] > 0,

"Gain",

IF ( Data[Expected result CC] < 0, "Loss" )

)

[kb856]

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 🙂