Forum Discussion

ArvindJha's avatar
ArvindJha
Helper III
1 year ago
Solved

Fetch data from previous row

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: ...
Need Help
  • TomMartens's avatar
    TomMartens
    1 year ago

    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

TomMartens's avatar
TomMartens
Super User
1 year ago

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