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

  • ArvindJha's avatar
    ArvindJha
    Helper III
    1 year ago

    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's avatar
      TomMartens
      Super User
      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

      • ArvindJha's avatar
        ArvindJha
        Helper III
        1 year ago

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