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 
Yeah It is working in measure, but I am prefering calculated column beacuse I need Sum of this and in measure it is giving incorrect value, or I am not sure to fix it in measure..
Can you help me with calculated column or measure which would aggregate same value as in calculated column?

Thanks and Regards
Mihir

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

Please check the below DAX formula for creating a calcualted column.

It will take quite a long time to load the newly created column, but I could not optimize it for now. (On my computer, it took more than 3 mintues to load a calcualted column).

My suggestion is still creating a measure to show the same result.

Thanks.

 

Energy_consum CC =
VAR _configtable =
    FILTER ( FloatTable, FloatTable[TagIndex] = EARLIER ( FloatTable[TagIndex] ) )
VAR Prev_datetime =
    MAXX (
        FILTER (
            _configtable,
            FloatTable[Date] = EARLIER ( FloatTable[Date] )
                && FloatTable[Time] < EARLIER ( FloatTable[Time] )
        ),
        FloatTable[Time]
    )
VAR Prev_date =
    MAXX (
        FILTER ( _configtable, FloatTable[Date] < EARLIER ( FloatTable[Date] ) ),
        FloatTable[Date]
    )
VAR Prev_time =
    MAXX ( FILTER ( _configtable, FloatTable[Date] = Prev_date ), FloatTable[Time] )
RETURN
    FloatTable[Val]
        - SWITCH (
            TRUE (),
            NOT ISBLANK ( Prev_datetime ),
                MAXX (
                    FILTER (
                        _configtable,
                        FloatTable[Time] = Prev_datetime
                            && FloatTable[Date] = EARLIER ( FloatTable[Date] )
                    ),
                    FloatTable[Val]
                ),
            MAXX (
                FILTER (
                    _configtable,
                    FloatTable[Time] = Prev_time
                        && FloatTable[Date] = Prev_date
                ),
                FloatTable[Val]
            )
        )