Measure to filter out sales in a specific date range

[Reine]

Hello - I have a matrix set up with columns for current month sales, YTD sales and my total Previous year sales.  This Matrix uses a slicer so I can select the current month/year. 

To get the total previous year sales and bypass the slicer, I use this measure:  

CALCULATE (SUM ('report Charge'[TotalChargeAmount]),PREVIOUSYEAR('Date table'[Date]))

My issue is that I also need a column for the total Previous year sales WITHOUT April, May, June.  I have tried everything I can think of, with my very limited DAX understanding, and just cannot figure out how to write a measure to filter out those months from my total.

Any help is appreciated 🙂

[Anonymous]

@Reine 

The code was fine. On a completely different line I used CALCULATE instead of CALCULATETABLE. Hence the problem. But the measure does exactly what you were looking for.

See this: https://dax.do/Wty121eh8QDrH2/ 

[Anonymous · Accepted solution]

[Edit] I've amended the measure so that it now works. The logic was OK even before but I used CALCULATE instead of CALCULATETABLE. This is where the problem was. I write measures without models, so I don't get a chance to debug. I just trust my experience and if I write late in the night.... well, I'm already tired and mistakes creep in. Sorry!

[Sales w/o April, May June] =
var PrevYearMonths =
    CALCULATETABLE(
        DISTINCT( 'Date table'[Date] ),
        PREVIOUSYEAR( 'Date table'[Date] ),
        // If you don't have a column called
        // Month Name in the dates table, use
        // anything that will identify full
        // months in each year. So the months' identifier
        // must have the same values for the same month
        // in each year. You'll have to adjust the values
        // in the curly braces as well in such a case.
        NOT 'Date table'[Month Name] IN {
            "April", "May", "June"
        },
        ALL( 'Date table' )
    )
var Result =
    CALCULATE(
        [Total Charge Amount], // the base measure
        PrevYearMonths
    )
return
    Result

[Reine]

Thank you for answering 🙂  However, this does not work for me.  The error is " The syntax for 'NOT' is incorrect.  

[Anonymous]

It was easy to amend... Just put NOT in front of the expression, not in front of IN. You, guys, should think a bit more 🙂.

Instead of 

        'Date table'[Month Name] NOT IN {
            "April", "May", "June"
        }

put

        NOT 'Date table'[Month Name] IN {
            "April", "May", "June"
        }

[Reine]

Actually I did try that last night and it also does not work.  I spent several hours trying diufferent things with the ideas your code gave me, and reviewed the NOT and IN functions and nothing I have tried is working.  The error I usually get is "The true/false expression does not specify a column"

[Anonymous]

@Reine 

Sorry, I don't believe you. I'm almost 100% sure the code is fully functional. Have done such things countless times in the past. Maybe you should update your PBI? I'll try to prove to you it DOES WORK correctly using http://dax.do. Stay tuned. I'll be back.