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 =
    MAXX(FILTER(
        ALL(FloatTable[Time]),
        FloatTable[Time] < SELECTEDVALUE(FloatTable[Time])
        ),
    FloatTable[Time]
    )
RETURN
CALCULATE(SUMX(FloatTable,
    VAR Index = FloatTable[TagIndex]
    RETURN
    FloatTable[Val]), FILTER(ALL(FloatTable), FloatTable[Time] = Prev_date && FloatTable[TagIndex] = Index ))
 
everything is going perfect, but on some dates this is giving blank rows....IDK why?
 

 


Can anyone help me?

Thanks and Regards
Mihir
  • 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
            )
        )
    )

23 Replies

  • 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.