Lastnonblank value minus 12

[mardennn]

Hi

I need two values each row. One is each row lastnonblank value (here I use LASTNONBLANK function) and value_12 = lastnonblank - 12.

For example: (I need value no 13 and no 1)

1   2,2

2   10,0

3   8,8

4   1,1

5   5,5

6   9,9

7   3,3

8   1,9

9   7,9

10  4,6

11  3,4

12  5,4

13  7,2

What I have to do in PowerBi?

[v-jiascu-msft]

Hi @mardennn,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft]

Hi @mardennn,

1. As a calculated column.

CalcultedColumn =
VAR lastColumn1 =
    MAX ( [Column1] )
RETURN
    CALCULATE (
        VALUES ( Table1[Value_12] ),
        'Table1'[Column1]
            IN { lastColumn1, lastColumn1 - 12 }
    )

2. As a measure.

Measure =
VAR lastColumn =
    CALCULATE ( MAX ( 'Table1'[Column1] ), ALL ( 'Table1' ) )
RETURN
    CALCULATE (
        MAX ( 'Table1'[Value_12] ),
        FILTER ( 'Table1', 'Table1'[Column1] IN { lastColumn, lastColumn - 12 } )
    )

If these aren't what you want, please post a sample of the result.

Best Regards!

Dale

[mardennn]

Hi

I need two values each row. One is each row lastnonblank value (here I use LASTNONBLANK function) and value_12 = lastnonblank - 12.

For example: (I need value no 13 and no 1)

1   2,2

2   10,0

3   8,8

4   1,1

5   5,5

6   9,9

7   3,3

8   1,9

9   7,9

10  4,6

11  3,4

12  5,4

13  7,2

What I have to do in PowerBi?