Get Value X Rows before/ every Xth row

Question

Hello everybody,I would like to display the value of a special row (10th row) along with the last/most recent possible value to divide these two numbers. This should be done for each group (A, B etc.). To illustrate the desired result, use the screenshot (generated in Excel):The first three columns are already in the Power Bi file. I imagine maybe creating one index column per group, where the close value is taken for every 10th row (index = 10). (marked in color, it is always the 10th row) Then the last most recent close (index = 0) should be divided by the value at index = 10. Maybe someone have a solution for that? The Power BI file with the three first columns can be found here:&nbsp;https://we.tl/t-28NZO8BN4Z&nbsp;Thank you!Kind Regards, hwoe&nbsp;

danextian · Accepted Answer

Hi&nbsp;hwoehler ,Try this:Create a calculated column for the Index within a group.&nbsp;&nbsp;Index Per Group =
RANKX (
    FILTER (
        Performance130,
        Performance130[Group] = EARLIER ( Performance130[Group] )
    ),
    Performance130[Date],
    ,
    ASC,
    Dense) - 1
​a&nbsp;&nbsp;Create this measure:&nbsp;&nbsp;Value =
VAR __10 =
    CALCULATE (
        SUM ( Performance130[Close] ),
        Performance130[Index Per Group] = 10
    )
VAR __0 =
    CALCULATE ( SUM ( Performance130[Close] ), Performance130[Index Per Group] = 0 )
RETURN
    DIVIDE ( __0, __10 )
​&nbsp;&nbsp;Use Group column and Value measure in a visual.

Forum Discussion

Get Value X Rows before/ every Xth row

1 Reply

Related content

Replace date values before specific date or error value to Null

Filter out date values before year 2000

Merged Values Change Before and After Query

Return Values Before a Certain Date

Calculating a week before value