Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
joshua1990
Post Prodigy
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

View solution in original post

8 REPLIES 8
mahoneypat
Employee
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
AntrikshSharma
Community Champion
Community Champion

What is the definition of a completed month?

Thanks for the reply!

A month is completed when every day is in the past

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.