cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Use EOMONTH to filter the next month on CALCULATE

Hi,

I'm using the following formula to filter a month from CALCULATE. It's working, but if I change the second argument of the EOMONTH to 1, to filter the next month it doesn't work. Does someone know how to fix it?


CALCULATE(
    SUM(fSales[Volume]) / 1000;
    FILTER(
        fSales;
fSales[Attribute] = "Fiscal"
    );
    FILTER(
        dCalendar;
        ENDOFMONTH(dCalendar[Date]) <> EOMONTH(SELECTEDVALUE(fProjectedProduction[Date]);0)
    )
)

 

Thanks in advance,
Cidcley Barbosa

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Use EOMONTH to filter the next month on CALCULATE

The main rule of DAX filtering states: Do not filter a table if you can filter a column. There is a good reason behind it related to performance. For more info please turn to www.sqlbi.com's articles 🙂

 

Secondly, I have no idea why it does not work. It does look like it should... but I don't know all the details, so can't say authoritatively.

 

Try this one:

 

Sales Qty A Fiscal (M) = 
var __month = SELECTEDVALUE( fProjectedProduction[Date] )
VAR __monthFormatted = FORMAT( __month; "yy-mm" )
var __nextMonth = EDATE( __month; 1 )
VAR __nextMonthFormatted = FORMAT( __nextMonth; "yy-mm" )
RETURN
CALCULATE(
    SUM( fSales[Volume] ) / 1000; -- please turn this into a measure on its own
    KEEPFILTERS( fSales[Attribute] = "Fiscal" );
    EXCEPT(
        -- Should this not be [Sort mm-yy]?
        -- m is used in formatting to have a number 1,2,3,...,12
        -- mm is used to have a string 01,02,03,...,12
        -- mmm is used to have a short name Jan,Feb,...,Dec
        -- mmmm is used to have a full name January,February,...
        VALUES( dCalendar[Sort mmm-yy] );
        UNION(
            {__Month };
            {__NextMonth}
        )
    )
)

If you can't get this to work, then you'll have post a link to an example file via OneDrive or GoogleDrive, or Dropbox... or anything like this.

 

Best

Darek

 



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Use EOMONTH to filter the next month on CALCULATE

I don't know what it means "it doesn't work" since you've not provided any screenshot and have not explained exactly how it should work. But based on this incomplete information, I've changed the code a bit:

 

var __projProductionDate = SELECTEDVALUE(fProjectedProduction[Date]) 
var __endOfMonth = EOMONTH( __projProductionDate; 0)
return
CALCULATE(
    SUM(fSales[Volume]) / 1000;
    keepfilters( fSales[Attribute] = "Fiscal" );
    filter(
    	VALUES( dCalendar[Date] );
        ENDOFMONTH( dCalendar[Date] ) <> __endOfMonth 
    )
)

You might want to change 0 to 1 or -1 (or anything else) and see if this works.

 

By the way, if you want to filter out a month, why don't you use the most obvious and best way? Your calendar should have fields that store months and years. If it does have them, then the measure would be:

 

var __projProductionDate = SELECTEDVALUE(fProjectedProduction[Date])
var __endOfMonth = EOMONTH( __projProductionDate; 0)
var __projProductionMonth = MONTH( __endOfMonth )
var __projProductionYear = YEAR( __endOfMonth )
var __dates =
	EXCEPT(
		VALUES( dCalendar[Date] ),
		CALCULATETABLE(	
			VALUES( dCalendar[Date] ),
			dCalendar[Month] = __projProductionMonth,
			dCalendar[Year] = __projProductionYear
		)
	)
return
CALCULATE(
    SUM(fSales[Volume]) / 1000;
    keepfilters( fSales[Attribute] = "Fiscal" );
    __dates,
    ALL( dCalendar ) -- this line is most likely redundant
)

 

Best

Darek



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Advocate I
Advocate I

Re: Use EOMONTH to filter the next month on CALCULATE

Hi Darek,

 

I'm trying to exclude two months, the following code works as expected:

 

 

Sales Qty A Fiscal (M) = 
CALCULATE(
    SUM(fSales[Volume]) / 1000;
    FILTER(
        fSales;
        fSales[Attribute] = "Fiscal"
    );
    FILTER(
        dCalendar;
        NOT dCalendar[Sort mmm-yy] IN {"19-06"; "19-07"}
    )
)

But, if I try to use month dynamically through a measure the VAR __NextMonth doesn't work (values from 19-07 are not filtered):

Sales Qty A Fiscal (M) = 
VAR __Month = FORMAT(SELECTEDVALUE(fProjectedProduction[Date]);"yy-mm")
VAR __NextMonth = FORMAT(EDATE(SELECTEDVALUE(fProjectedProduction[Date]);1);"yy-mm")
RETURN
CALCULATE(
    SUM(fSales[Volume]) / 1000;
    FILTER(
        fSales;
        fSales[Attribute] = "Fiscal"
    );
    FILTER(
        dCalendar;
        NOT dCalendar[Sort mmm-yy] IN {__Month; __NextMonth}
    )
)

Cheers,
Cidcley

 

Highlighted
Super User IV
Super User IV

Re: Use EOMONTH to filter the next month on CALCULATE

The main rule of DAX filtering states: Do not filter a table if you can filter a column. There is a good reason behind it related to performance. For more info please turn to www.sqlbi.com's articles 🙂

 

Secondly, I have no idea why it does not work. It does look like it should... but I don't know all the details, so can't say authoritatively.

 

Try this one:

 

Sales Qty A Fiscal (M) = 
var __month = SELECTEDVALUE( fProjectedProduction[Date] )
VAR __monthFormatted = FORMAT( __month; "yy-mm" )
var __nextMonth = EDATE( __month; 1 )
VAR __nextMonthFormatted = FORMAT( __nextMonth; "yy-mm" )
RETURN
CALCULATE(
    SUM( fSales[Volume] ) / 1000; -- please turn this into a measure on its own
    KEEPFILTERS( fSales[Attribute] = "Fiscal" );
    EXCEPT(
        -- Should this not be [Sort mm-yy]?
        -- m is used in formatting to have a number 1,2,3,...,12
        -- mm is used to have a string 01,02,03,...,12
        -- mmm is used to have a short name Jan,Feb,...,Dec
        -- mmmm is used to have a full name January,February,...
        VALUES( dCalendar[Sort mmm-yy] );
        UNION(
            {__Month };
            {__NextMonth}
        )
    )
)

If you can't get this to work, then you'll have post a link to an example file via OneDrive or GoogleDrive, or Dropbox... or anything like this.

 

Best

Darek

 



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


View solution in original post

Highlighted
Advocate I
Advocate I

Re: Use EOMONTH to filter the next month on CALCULATE

Thank for assistance Darek!

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors