Use EOMONTH to filter the next month on CALCULATE

[CidcleyBarbosa]

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

[Anonymous]

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

[CidcleyBarbosa]

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

[Anonymous · Accepted solution]

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

[CidcleyBarbosa]

Thank for assistance Darek!