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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

Thank you for your feedback.

Could you please try the below? and please check the attached pbix file.

 

- Delete Calculated column that I created.

- Create three measures like below.

- Please check if the numbers are correct.

- If the numbers are correct, no need to create a Calcualted column.

 

Val Sum: = 
SUM( FloatTable[Val] )
Prev_value_Energy_consum = 
var _currentdate = MAX( FloatTable[Date])
var _currenttime = MAX(FloatTable[Time])
VAR Prev_datetime =
    MAXX (
        FILTER (
            ALLSELECTED ( FloatTable ),
            FloatTable[Date] = _currentdate
                && FloatTable[Time] < _currenttime
        ),
        FloatTable[Time]
    )
VAR Prev_date =
    MAXX (
        FILTER (
            ALLSELECTED ( FloatTable ),
            FloatTable[Date] < _currentdate
        ),
        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, FloatTable[Val] ),
                FILTER (
                    ALLSELECTED ( FloatTable ),
                    FloatTable[Time] = Prev_datetime
                        && FloatTable[Date] = _currentdate
                        
                )
            ),
        CALCULATE (
            SUMX ( FloatTable, FloatTable[Val] ),
            FILTER (
                ALLSELECTED ( FloatTable ),
                FloatTable[Time] = Prev_time
                    && FloatTable[Date] = Prev_date
                    
            )
        )
    )
Test measure: = 
IF (
    HASONEVALUE ( FloatTable[Time] ),
    [Val Sum:] - [Prev_value_Energy_consum],
    SUMX ( FloatTable, [Val Sum:] - [Prev_value_Energy_consum] )
)