# Power BI refer to previous row

## Background

sample data

![](https://932810926-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M3yezE44r25qTEGkEjz%2F-MdWmDQZbBsscPtx3MWx%2F-MdWmj0szL1jucbZndm9%2Fimage.png?alt=media\&token=c51d4feb-4f21-4d10-82ac-85718408775f)

Formula is like:

```
result = 
if [previous row exists]: 
    (last(target) - first(target))/11 + <previous row value> 
else: 
    first(target)
```

> In PowerBI we cannot refer to self (e.g. previous row same column), but can refer to previous row another column.&#x20;

So we can make kind of math transformation:

```
row0 = 1.7
row1 = (2.15 - 1.7)/11  + 1.7
row2 = (2.15 - 1.7)/11 + row1 =  (2.15 - 1.7)/11 * 2 + 1.7
row3 = (2.15 - 1.7)/11 + row2 = (2.15 - 1.7)/11 * 3 + 1.7
...
```

The transformed formula:

```
result = 
(last(target) - first(target))/11 * <index of row> + first(target)
```

## Step

* Load Data
* Add Index Column

![](https://932810926-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M3yezE44r25qTEGkEjz%2F-MdWmDQZbBsscPtx3MWx%2F-MdWrF44Jpwd3WBgTBDX%2Fimage.png?alt=media\&token=db66457b-de9d-416b-8307-406cb86efa89)

* Add Custom Column 'result', make formula:

![](https://932810926-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M3yezE44r25qTEGkEjz%2F-MdWmDQZbBsscPtx3MWx%2F-MdWrthts2XzNYzMze8G%2Fimage.png?alt=media\&token=9bbbbbed-5f63-4f7e-b97d-03234860c2c8)

```
( List.Last(#"Added Index" [target])  - List.First(#"Added Index" [target]))/11 * #"Added Index" {[Index]}[Index] + List.First(#"Added Index" [target])
```

> the `List.Last` can be replace by `List.Max`, and `List.First` can be replaced by `Lits.Min` regard of context.

* Final Result:

![](https://932810926-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M3yezE44r25qTEGkEjz%2F-MdWmDQZbBsscPtx3MWx%2F-MdWsf5fBJpLNHaYuAV9%2Fimage.png?alt=media\&token=824c2f74-e7cf-4cd7-9263-19e2e11933d4)

## References

{% embed url="<https://community.powerbi.com/t5/DAX-Commands-and-Tips/Find-value-of-Previous-Row-in-same-column/m-p/1202212>" %}

{% embed url="<https://www.youtube.com/watch?v=UPddzZnsf5w>" %}
