Fetch data from previous row

Hey ArvindJha ,

You achieve what you want using DAX by creating calculated columns by leveraging the OFFSET function. Keep in mind that it's not possible to overwrite existing data, this means you need to create to columns for height and weight my example showcases only HEIGHT
The first calculated column that find the previous value

HEIGHT_ = 
var currentpatient = 'Table'[PATIENTID]
var prevDate = 
SELECTCOLUMNS(
    OFFSET(
        -1,
        SUMMARIZE(
        ALLSELECTED( 'Table' ),
        'Table'[PATIENTID],
        'Table'[DATE],'Table'[SITE]
        ),
        ORDERBY( 'Table'[DATE] , ASC),
        PARTITIONBY( 'Table'[PATIENTID], 'Table'[SITE])
    ),
    "d", 'Table'[DATE]
)
return
CALCULATE( 
    MIN(  'Table'[HEIGHT] ),
    ALL('Table'),
    'Table'[DATE] IN prevdate,
    'Table'[PATIENTID] = currentpatient
)

The 2nd calculated column that is mixing it together

HEIGHT NEW = 
var currentHeight = 'Table'[HEIGHT]
RETURN
IF( ISBLANK( currentHeight ) , 'Table'[HEIGHT_] , currentHeight)

This how the table will look like:

Hopefully this provides what you are looking for.

Regards,

Tom

ArvindJha ,

You can use fill down option from Power Query Editor. Select the two columns and apply fill down,

OUTPUT:

check this doc - https://learn.microsoft.com/en-us/power-query/fill-values-column

ArvindJha 

Go to Trasnform Data.

Go to Add Column > Index Column > From 1.

Right-click and choose Fill Down to fill missing values from the previous non-null value for each patient.

Go to Transform > Group By > Select All Rows.

Use a custom column to fetch previous values with:

if [Index] = 1 then [HEIGHT] else Table.SelectRows(PreviousStep, each _[Index] = [Index]-1){0}[HEIGHT]

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Hey ArvindJha ,

You achieve what you want using DAX by creating calculated columns by leveraging the OFFSET function. Keep in mind that it's not possible to overwrite existing data, this means you need to create to columns for height and weight my example showcases only HEIGHT
The first calculated column that find the previous value

HEIGHT_ = 
var currentpatient = 'Table'[PATIENTID]
var prevDate = 
SELECTCOLUMNS(
    OFFSET(
        -1,
        SUMMARIZE(
        ALLSELECTED( 'Table' ),
        'Table'[PATIENTID],
        'Table'[DATE],'Table'[SITE]
        ),
        ORDERBY( 'Table'[DATE] , ASC),
        PARTITIONBY( 'Table'[PATIENTID], 'Table'[SITE])
    ),
    "d", 'Table'[DATE]
)
return
CALCULATE( 
    MIN(  'Table'[HEIGHT] ),
    ALL('Table'),
    'Table'[DATE] IN prevdate,
    'Table'[PATIENTID] = currentpatient
)

The 2nd calculated column that is mixing it together

HEIGHT NEW = 
var currentHeight = 'Table'[HEIGHT]
RETURN
IF( ISBLANK( currentHeight ) , 'Table'[HEIGHT_] , currentHeight)

This how the table will look like:

Hopefully this provides what you are looking for.

Regards,

Tom

Hello ArvindJha 

You can generate a calculated column in a table using DAX. For the filled height, apply the DAX formula provided below:

Filled Height = 
VAR _patientID = Tbl[PATIENTID]
VAR _date = Tbl[DATE]

RETURN
IF(
    Tbl[HEIGHT] = BLANK(),
    CALCULATE(
        MAX(Tbl[HEIGHT]),
        FILTER(
            Tbl,
            Tbl[PATIENTID] = _patientID && Tbl[DATE] < _date
        )
    ), Tbl[HEIGHT]
)

For the filled weight, simply substitute [HEIGHT] with [WEIGHT] in the formula:

Filled Weight = 
VAR _patientID = Tbl[PATIENTID]
VAR _date = Tbl[DATE]

RETURN
IF(
    Tbl[WEIGHT] = BLANK(),
    CALCULATE(
        MAX(Tbl[WEIGHT]),
        FILTER(
            Tbl,
            Tbl[PATIENTID] = _patientID && Tbl[DATE] < _date
        )
    ), Tbl[WEIGHT]
)

Below is the screenshot displaying the resulting table in Power BI:

Note: Make sure that the [DATE] column datatype is "Date" and not "Text"

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

To fetch data from the previous row for each patient and site (similar to Qlik’s `Peek` function) in Power BI, you can use **DAX** in a calculated column. Here’s how to approach it:

1. **Use the `EARLIER` Function**: Create calculated columns that reference the previous row for `HEIGHT` and `WEIGHT`.

### Example DAX:
```DAX
Previous_Height =
IF(
ISBLANK('Table'[Height]),
CALCULATE(MAX('Table'[Height]),
FILTER('Table',
'Table'[PATIENTID] = EARLIER('Table'[PATIENTID]) &&
'Table'[SITE] = EARLIER('Table'[SITE]) &&
'Table'[DATE] < EARLIER('Table'[DATE])
)
),
'Table'[Height]
)

Previous_Weight =
IF(
ISBLANK('Table'[Weight]),
CALCULATE(MAX('Table'[Weight]),
FILTER('Table',
'Table'[PATIENTID] = EARLIER('Table'[PATIENTID]) &&
'Table'[SITE] = EARLIER('Table'[SITE]) &&
'Table'[DATE] < EARLIER('Table'[DATE])
)
),
'Table'[Weight]
)
```

This logic fetches the previous `Height` and `Weight` for the same patient and site when current values are blank.