Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Reine
Helper IV
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

[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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.