Calculate delta between the selected month and last month of the previous quarter

[TUSHARMODGIL]

I have written a calcualtion to get the delta between the selected month and last month of the previous quarter. In this calculation, I am getting the value for current month correctly, but the "Metric_lastQtr" is giving null. I checked the return value for "LastDatePrevQtr" and it is giving me the correct date for last month of the quarter. 

I have tried multiple things here but can't resolve it. Would be really helpful if someone can correct this formula:

Delta_Quarterly =

VAR CurrentMonth = MONTH(SELECTEDVALUE(FactScorecard[Date]))

-- Determine the last quarter month

VAR LastDatePrevQtr =

    CALCULATE(

        MAX(DimDate[Date]),

        PREVIOUSQUARTER(DimDate[Date])

    )

VAR Metric_CurrentMonth =

    CALCULATE(

        SUM(FactScorecard[Value]),

        DimDate[MonthNumber] = CurrentMonth

    )

VAR Metric_lastQtr =

    CALCULATE(

        SUM(FactScorecard[Value]),

        REMOVEFILTERS(DimDate),

        ALLEXCEPT(DimDate, DimDate[MonthYearNameShort]),

        DimDate[Date] = LastDatePrevQtr

    )

RETURN

    Metric_CurrentMonth - Metric_lastQtr

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your semantic model.

Delta_last quarter last month: = 
VAR _lastquarter =
    CALCULATETABLE ( DimDate, PREVIOUSQUARTER ( DimDate[Date] ) )
VAR _lastquarterlastmonthenddate =
    MAXX ( _lastquarter, DimDate[Year-Month sort] )
VAR _lastquarterlastmonthvalue =
    CALCULATE (
        SUM ( FactScorecard[Value] ),
        FILTER (
            ALL ( DimDate ),
            DimDate[Year-Month sort] = _lastquarterlastmonthenddate
        )
    )
VAR _currentmonthvalue =
    SUM ( FactScorecard[Value] )
RETURN
    IF (
        NOT ISBLANK ( _lastquarterlastmonthvalue ),
        _currentmonthvalue - _lastquarterlastmonthvalue
    )

[TUSHARMODGIL]

It worked like a charm. 

I had to add [Year-Month sort] to my calendar table and then used your calcualtion as-is.

Thanks!!!