Measure Optimization - Nesting IF inside filter section of calculate

[Anonymous]

I am trying to optimize this lagging  measure.  The following works, and gives me the desired outcome. Basically, if the month interval is 12 (December), return the first filter.  If not, return the second.  The basis of the  measure is to look at the selected month (determined in a slicer), and then return the values for the next month.

NextMonthJobsStartingTest = 
IF (
    SELECTEDVALUE ( 'Calendar'[MonthInt] ) = 12,
    CALCULATE (
        [SUMTotalAmt],
        FILTER (
            Sales,
            Sales[ProjectedStartDateMonthInt] = 1
                && Sales[ProjectedStartDateYear]
                    = SELECTEDVALUE ( 'Calendar'[Year] ) + 1
        )
    ),
    CALCULATE (
        [SUMTotalAmt],
        FILTER (
            Sales,
            Sales[ProjectedStartDateMonthInt]
                = SELECTEDVALUE ( 'Calendar'[MonthInt] ) + 1
                && Sales[ProjectedStartDateYear] = SELECTEDVALUE ( 'Calendar'[Year] )
        )
    )
)

I'm trying to parse this down and make it more efficient with the following.

NextMonthJobsStartingTest2 = 
CALCULATE (
    [SUMTotalAmt],
    IF (
        SELECTEDVALUE ( 'Calendar'[MonthInt] ) = 12,
        FILTER (
            Sales,
            Sales[ProjectedStartDateMonthInt] = 1
                && Sales[ProjectedStartDateYear]
                    = SELECTEDVALUE ( 'Calendar'[Year] ) + 1
        ),
        FILTER (
            Sales,
            Sales[ProjectedStartDateMonthInt]
                = SELECTEDVALUE ( 'Calendar'[MonthInt] ) + 1
                && Sales[ProjectedStartDateYear] = SELECTEDVALUE ( 'Calendar'[Year] )
        )
    )
)

I recieve the following error message:

It appears that the measure doesn't like using IF() to determine which filter to select... What other options can I use?  Also, is there a better way to trim down this measure?

TIA

[Vera_33 · Accepted solution]

Hi @Anonymous 

You need to determin the next month period, try this way, assume you have Date column in Calendar

NextMonthJobsStartingTest = 
VAR CurDate = DATE(SELECTEDVALUE ( 'Calendar'[Year] ),SELECTEDVALUE( 'Calendar'[MonthInt] ),1)
VAR MinDate = EDATE(CurDate,1)
VAR MaxDate = EDATE(CurDate,2)
RETURN
SUMX(FILTER(ALL('Calendar'),'Calendar'[Date]>=MinDate&&'Calendar'[Date]<MaxDate),[SUMTotalAmt])

[Anonymous]

@Vera_33.  Thanks so much.  This did work.  The only change I added was to use 'Sales'[ProjectedStartDate] in place of 'Calendar'[Date].  I need to filter based on the Projected Start Date...

As well, I had to drop the All('Calendar') piece of the filter.  Any idea why that would be?