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
Anonymous
Not applicable

[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
Anonymous
Not applicable

@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
Not applicable

[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

 

 

 

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

Anonymous
Not applicable

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"

Anonymous
Not applicable

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

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.