cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper IV

## 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

 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``````

6 REPLIES 6
Anonymous
Not applicable

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

 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``````

Helper IV

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

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

put

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

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

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors