Fetch data from previous row

[ArvindJha]

Hi Team,

I want to fetch data from previous row and create some if condition based on the values , it should restart for every patient and also be similar to Qlik peek function , thanks

Input:

PATIENTID	SITE	DATE	HEIGHT	WEIGHT
1	1	1st Aug 2024	49	59
1	1	2nd Aug 2024		62
1	1	3rd Aug 2024	54
1	1	4th Aug 2024		62
2	1	1st Aug 2024	49	57
2	1	2nd Aug 2024		54
2	1	3rd Aug 2024	56
2	1	4th Aug 2024		65
2	2	10th Aug 2024	55	71
3	1	1st Aug 2024
3	1	2nd Aug 2024	61	54
3	1	3rd Aug 2024
3	1	4th Aug 2024	56
3	2	10th Aug 2024	43	71

Expected Output

PATIENTID	SITE	DATE	HEIGHT	WEIGHT
1	1	1st Aug 2024	49	59
1	1	2nd Aug 2024	49	62
1	1	3rd Aug 2024	54	62
1	1	4th Aug 2024	54	62
2	1	1st Aug 2024	49	57
2	1	2nd Aug 2024	49	54
2	1	3rd Aug 2024	56	54
2	1	4th Aug 2024	56	65
2	2	10th Aug 2024	55	71
3	1	1st Aug 2024
3	1	2nd Aug 2024	61	54
3	1	3rd Aug 2024	61	54
3	1	4th Aug 2024	56	54
3	2	10th Aug 2024	43	71

[PavanLalwani]

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.

[quantumudit]

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

[ArvindJha]

@quantumudit thanks a lot , its close the only problem is max it takes max value from all the past records ,it should take the previous value instead of max

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

[quantumudit]

Hello @ArvindJha

The MAX() shouldn't be a problem.. CALCULATE() need to use a aggregate function, so you can use MIN() or AVERAGE() as well..

Have you tried it yet? If yes, then could you send me the snapshot of what are the discrepancies you are getting. 

[ArvindJha]

Hello @quantumudit , below is the screenshot

FilledWeight has the logic

Filled Weight =

VAR _patient = Sheet1[PATIENTID]

VAR _date = Sheet1[DATE]

RETURN

IF(

    Sheet1[WEIGHT] = BLANK(),

    CALCULATE(

        MAX(Sheet1[WEIGHT]),

        FILTER(

            Sheet1,

            Sheet1[PATIENTID] = _patient && Sheet1[DATE] <= _date

        )

    ),Sheet1[WEIGHT]

)

According to the logic its taking max though it should take the previous data instead of max.

Thanks

[TomMartens]

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]

Hi @TomMartens ,

it works except for 1 thing it shows blank if there are multiple null values in between , highlighted in yellow , should show 62 and 54 respectively

[TomMartens · Accepted solution]

Hey @ArvindJha ,
I use the below DAX to create a calculated column 

WEIGHT_ = 
var currentPatientID = 'Table'[PATIENTID]
var currentSite = 'Table'[SITE]
var prevDate = 
    maxx(
        SELECTCOLUMNS(
            FILTER(
                WINDOW(
                    1, abs, -1, rel,
                    SUMMARIZE(
                        ALLSELECTED( 'Table' ), 
                        'Table'[PATIENTID],
                        'Table'[SITE],
                        'Table'[DATE],
                        'Table'[WEIGHT]
                    ),
                    ORDERBY( 'Table'[DATE] , ASC),
                    PARTITIONBY( 'Table'[PATIENTID] , 'Table'[SITE])
                ),
                NOT( ISBLANK( 'Table'[WEIGHT] ) )
            ),
            "d" , [DATE]
        )
        , [d]
    )
return
CALCULATE(
    MAX('Table'[WEIGHT]),
    ALL('Table'),
    'Table'[PATIENTID] = currentPatientID,
    'Table'[SITE] = currentSite,
    'Table'[DATE] = prevDate
)

The table looks like this

Hopefully, this provides what you are looking for.
If not, read this article: https://www.minceddata.info/2024/03/09/my-favorite-windowing-function-window/

Regards,

Tom

[ArvindJha]

@TomMartens Thanks it works , also thanks for sharing the link , it seems useful

[TomMartens]

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

[Kedar_Pande]

@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

[Arul]

@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]

@Arul Thanks for the response the only catch is it should not fill down when Patient Id changes , it should restart , so if you see my expected output for Patient Id 3 (1st row) it should be blank as we do not have data in 1st row