cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Post Prodigy

## Filter just for completed months

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable
``````// 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

8 REPLIES 8
Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Post Prodigy

@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
Not applicable
``````// 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

Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Post Prodigy

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
Not applicable
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
Community Champion
What is the definition of a completed month?
Post Prodigy

Thanks for the reply!

A month is completed when every day is in the past