Rolling 5 row Difference

Question

Hello,&nbsp;&nbsp;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.&nbsp;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).&nbsp;I will be forever thankful if someone can help. Thanks!&nbsp;&nbsp;

jihwan_kim · Accepted Answer

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.
&nbsp;

&nbsp;

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 &lt;&gt; BLANK (), currentvalue - previousvalue )
&nbsp;

Gain or Loss CC = 
IF (
Data[Expected result CC] &gt; 0,
"Gain",
IF ( Data[Expected result CC] &lt; 0, "Loss" )
)

Forum Discussion

Rolling 5 row Difference

2 Replies

Related content

Rolling accumulation

Rolling accumulation

Matrix Row Filtering Alters Rolling DATESINPERIOD Measures

Difference between two rows

Dynamic Row-Level Calculation in Matrix (Different Logic per Row) - Scalable Approach?