Filter just for completed months

[joshua1990]

Hello everybody!

I need to add a column to my calendar to filter in my views just for completed months.

The calendar I am using is a fiscal calendar like

Date - Fiscal Month

Does someone know any dax function to get a False/ True, of the date is within a completed fiscal month?

[mahoneypat]

This should get your desired result in a calculated column (this example is on the Date table).

Month Complete = IF(EOMONTH('Date'[Date],0)<=TODAY(), "Yes", "No")

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[SPillai]

That's really clever  👍   Just one line to cater for end of month check, amazing!

[JezzatheGee]

Perfect - very simple but does the job!!

[joshua1990]

@mahoneypat Thanks a lot! Are you sure this works with a fiscal month with individual end dates for each month (mentioned in starting post)?

[Anonymous · Accepted solution]

// Let's say that FiscalMonthID is
// a unique identifier of a fiscal
// month in Calendar.

[Full Fiscal Month] = // calculated column
var __today = TODAY()
var __currentFiscalMonth = Calendar[FiscalMonthID]
var __allFiscalMonthDatesAreOK =
	ISEMPTY(
		FILTER(
			'Calendar',
			Calendar[FiscalMonthID] = __currentFiscalMonth
			&&
			Calendar[Date] > __today
		)
	)
RETURN
	__allFiscalMonthDatesAreOK

Best
D

[mahoneypat]

It won't work for fiscal months. I forgot about that part.  What is your logic to determine the fiscal month.  If you share it, I will try an alternate expression.

Regards,

Pat

[joshua1990]

The fiscal months are based on the fiscal weeks:

Week 1-4 = January

Week 5-8 = February

Week 9-13 = March

The measure is:

VAR FiscalWeek =WEEKNUM ( Date, 21 )

[Anonymous]

Just create a column where you'll store FiscalMonthID the way I indicated in the piece of code above and you'll be able to create the column. You should have FiscalMonthID in the table regardless.

Best
D

[AntrikshSharma]

What is the definition of a completed month?

[joshua1990]

Thanks for the reply!

A month is completed when every day is in the past