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

ArvindJha's avatar
ArvindJha
Helper III
1 year ago

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'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