Forum Discussion

JJ_masgio's avatar
JJ_masgio
Regular Visitor
3 years ago
Solved

Calculate expression by previous row

Hi! Quite new to DAX. This is my fact table: Date (dd-mm-yyyy) Value 03-06-2023 987 23-06-2023 1112 02-07-2023 1234 12-07-2023 1543 I'm interested in the delta Value across...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi JJ_masgio ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    Measure = 
    VAR _selyear =
        SELECTEDVALUE ( 'Calendar'[Date].[Year] )
    VAR _selmonth =
        SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] )
    VAR _curmvalue =
        CALCULATE (
            MAX ( 'Table'[Value] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                YEAR ( 'Table'[Date] ) = _selyear
                    && MONTH ( 'Table'[Date] ) = _selmonth
            )
        )
    VAR _premdate =
        EOMONTH ( DATE ( _selyear, _selmonth, 1 ), -1 )
    VAR _premvalue =
        CALCULATE (
            MAX ( 'Table'[Value] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                YEAR ( 'Table'[Date] ) = YEAR ( _premdate )
                    && MONTH ( 'Table'[Date] ) = MONTH ( _premdate )
            )
        )
    RETURN
        IF (
            ISBLANK ( _premvalue ) || ISBLANK ( _curmvalue ),
            BLANK (),
            _curmvalue - _premvalue
        )
    Delta = 
    IF (
        ISINSCOPE ( 'Calendar'[Date].[Year] ),
        [Measure],
        SUMX (
            GROUPBY ( 'Calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month] ),
            [Measure]
        )
    )

    Best Regards