topic Re: Filter just for completed months in DAX Commands and Tips

Filter just for completed months

joshua1990 — Sun, 05 Jul 2020 13:35:33 GMT

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?

Re: Filter just for completed months

AntrikshSharma — Sun, 05 Jul 2020 13:49:04 GMT

What is the definition of a completed month?

Re: Filter just for completed months

joshua1990 — Sun, 05 Jul 2020 14:26:03 GMT

Thanks for the reply!

A month is completed when every day is in the past

Re: Filter just for completed months

mahoneypat — Sun, 05 Jul 2020 23:11:35 GMT

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

Re: Filter just for completed months

joshua1990 — Mon, 06 Jul 2020 16:13:29 GMT

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

Re: Filter just for completed months

mahoneypat — Mon, 06 Jul 2020 22:38:56 GMT

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

Re: Filter just for completed months

Anonymous — Tue, 07 Jul 2020 08:46:40 GMT

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

Re: Filter just for completed months

joshua1990 — Tue, 07 Jul 2020 12:18:28 GMT

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 )

Re: Filter just for completed months

Anonymous — Tue, 07 Jul 2020 14:40:28 GMT

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

Re: Filter just for completed months

JezzatheGee — Wed, 03 Jul 2024 11:40:22 GMT

Perfect - very simple but does the job!!

Re: Filter just for completed months

SPillai — Thu, 20 Mar 2025 08:55:09 GMT

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