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 I also want to use measure only for this, but as you can see in the grand total, value is incorrect for the measure( it is same as the Val column but it shouldn't be), that is the main reason I am looking for any other approach

 

 

Thanks and Regards
Mihir

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Jihwan_Kim 
I used your formula for calculated column in my original file which has millions of rows, there I am not able to execute the calculated column, It is showing 'Not Enough Memory Error'.
I think this is because it is very complex formula with so many variables, we can try using the measure we created for previous value in the calculated column to optimize the formula...
But I am not able to put this all in a formula which shows correct value.

Thanks and Regards
Mihir