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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBI_Curve
Helper I
Helper I

Filter for all after this month

This might exist already but I've searched for the different phrasings I could think of.

 

I have 2 sets of values merged together, 1 is forecasts and the other is actuals. I use a basic If formula to show me the actuals if the associated date is on or before today, and the forecasts if the associated date is in the future. I need to change this from "everything after today" to "everything after this month".

 

At the moment, my formula is set as:

if ('Value'[date]<=now(), 'show me actual', 'show me forecast')

 

This filters out everything dated after today but still shows values that are dated later in this same month. 

Is there a simple way to just replace 'now()' with 'This Month' in some way?

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hey @PBI_Curve

 

I would recommend using the MONTH function to return the month number of NOW(). Also assuming that your data might have multiple years, you need to also specify that the year number of your data is less than the year number of NOW().

 

if (MONTH('Value'[date])<=MONTH(now()) && YEAR('Value'[date])<=YEAR(now()), 'show me actual', 'show me forecast')

Hope this helps,

Parker

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hey @PBI_Curve

 

I would recommend using the MONTH function to return the month number of NOW(). Also assuming that your data might have multiple years, you need to also specify that the year number of your data is less than the year number of NOW().

 

if (MONTH('Value'[date])<=MONTH(now()) && YEAR('Value'[date])<=YEAR(now()), 'show me actual', 'show me forecast')

Hope this helps,

Parker

Thanks @Anonymous, worked a treat!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.