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

quantumudit's avatar
quantumudit
Super User
1 year ago

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's avatar
ArvindJha
Helper III
1 year ago

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