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,

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

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        Could you please try writing a measure like below, instead of creating Calcualted Column?

         

        Test measure: = 
SUM(FloatTable[Val]) - [Prev_value_Energy_consum]