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

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      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
            )
        )
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Jihwan_Kim 

        Thankyou so much measure is working correct,

        but there is still one small issue remaining when I am trying to subtract the value from previous value using calculated column, then It is working fine but at same point of time when time difference b/w 2 consecutive rows is greater than 10 minutes it is showing same value rather than subtracting.
        I think this could beacuse we've used switch function in the measure and calculted column is confused which value to take, I may not correct I am just thinking out loud.
         

         

        Here is the sample file for the same : https://drive.google.com/drive/folders/1651tuMrez13NX48mrdh_UVSZuk7diGMP?usp=sharing

        Thanks and Regards
        Mihir