cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reine
Helper III
Helper III

Measure to filter out sales in a specific date range

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 🙂
 
 
1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

[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

 

 

 

View solution in original post

6 REPLIES 6
daxer
Solution Sage
Solution Sage

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

daxer
Solution Sage
Solution Sage

[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

 

 

 

View solution in original post

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

daxer
Solution Sage
Solution Sage

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

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"

daxer
Solution Sage
Solution Sage

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Users online (1,173)