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 

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