Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Getting Previous Row using DAX measure

Hello all,  I need to get previous record based on DateTime column and grouped by TagIndex column   I created this measure,   Prev_value_Energy_consum = VAR Index = [Index] VAR Prev_date =...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your sharing.

    Could you please try the below if it works?

     

    Prev_value_Energy_consum =
VAR Index = [Index]
VAR Prev_datetime =
    MAXX (
        FILTER (
            ALLSELECTED ( FloatTable ),
            FloatTable[Date] = SELECTEDVALUE ( FloatTable[Date] )
                && FloatTable[Time] < SELECTEDVALUE ( FloatTable[Time] )
        ),
        FloatTable[Time]
    )
VAR Prev_date =
    MAXX (
        FILTER (
            ALLSELECTED ( FloatTable ),
            FloatTable[Date] < SELECTEDVALUE ( FloatTable[Date] )
        ),
        FloatTable[Date]
    )
VAR Prev_time =
    MAXX (
        FILTER ( ALLSELECTED ( FloatTable ), FloatTable[Date] = Prev_date ),
        FloatTable[Time]
    )
RETURN
    SWITCH (
        TRUE (),
        NOT ISBLANK ( Prev_datetime ),
            CALCULATE (
                SUMX ( FloatTable, VAR Index = FloatTable[TagIndex] RETURN FloatTable[Val] ),
                FILTER (
                    ALLSELECTED ( FloatTable ),
                    FloatTable[Time] = Prev_datetime
                        && FloatTable[Date] = SELECTEDVALUE ( FloatTable[Date] )
                        && FloatTable[TagIndex] = Index
                )
            ),
        CALCULATE (
            SUMX ( FloatTable, VAR Index = FloatTable[TagIndex] RETURN FloatTable[Val] ),
            FILTER (
                ALLSELECTED ( FloatTable ),
                FloatTable[Time] = Prev_time
                    && FloatTable[Date] = Prev_date
                    && FloatTable[TagIndex] = Index
            )
        )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

I am not sure if I understood your question correctly, but please try the below measure whether it suits your requirement.

 

Prev_value_Energy_consum =
VAR Prev_date =
    MAXX (
        FILTER (
            ALLSELECTED ( FloatTable[Time] ),
            FloatTable[Time] < SELECTEDVALUE ( FloatTable[Time] )
        ),
        FloatTable[Time]
    )
RETURN
    CALCULATE (
        SUMX ( FloatTable, FloatTable[Val] ),
        FILTER ( ALLSELECTED ( FloatTable ), FloatTable[Time] = Prev_date )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Jihwan_Kim 

    Actually we have a column called 'TagIndex' which have values from 0 to 8000 and each Tag index refers to a pump located at different regions. So basically these are different pumps.
    And There is a column called 'Val' which has cumulative value coming from database, we need to subtract current row from previous row to convert it to non cumulative value.

    So that should be the main purpose of the logic, so I am creating a measure to get Prev_row for that record based on different tagindexes I select. after that I will create a calculated column which will subtract value from this measure.

     

    I tried your formula but it is still showing those blank values at some point of time as I showed in screenshot.

     

    Thanks and Regards
    Mihir

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      3 years ago

      Hi,

      Thank you for your feedback.

      Could you please share your sample pbix file's link (onedrive, googledrive, dropbox, any other) here, and then I can try to look into it to come up with a more accurate solution.

      Thank you.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hey Jihwan_Kim 
        Here is the link for the sample data : https://drive.google.com/drive/folders/1651tuMrez13NX48mrdh_UVSZuk7diGMP?usp=sharing

         

        In the table visual if you see 'Prev_row_energy_consum' field, there will be some blank rows in the table at some point of time...  I don't how to fix this...but I've noticed some pattern in the blank rows, let me try to explain.
        Actually in Time column we can see there is a difference of 10 minutes between any two consecutive rows....but at some point of time when this difference is more than 10 minutes then it is showing blank rows.... I have confirmed this by going to different dates and different tag indexes. Can you please help me fixing this?

        Thanks and Regards
        Mihir