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
spencer_g
Helper I
Helper I

Filter Page to Exclude Current Month

Hi everyone and thanks in advance. 

 

I have 3 years-worth of data that is continually being added to on a daily basis. At the end of each month I generate a report comparing that month and all months prior, however, I don't generate the report until a few days into the new month and then the line graphs in the report show data for the new month and I don't want to show any data for the month on this report. I want to be able to filter the entire page to exclude the current month. Does anyone know how to do this? 

 

Thanks in advance!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

One way would be to do something like this:

 

IsCurrentMonth = IF(MONTH([Date]) = MONTH(TODAY()),1,0)

Then just filter the page with IsCurrentMonth set to equal 0 only.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Guido_Beulen
Frequent Visitor

You can filter on calendermonth using relative date: this excludes the current month from the data shown: 

Guido_Beulen_0-1666944591187.png

The same is possible for years and weeks!

 

Guido_Beulen
Frequent Visitor

Do you need to setup a measure for Date first? How would you do it? Whenever I use [...] it only shows measures I have setup, it never shows the Date column in my Date table. 

@Greg_Deckler can you help on this? I have the same issue.

 

Thank you!

Greg_Deckler
Super User
Super User

One way would be to do something like this:

 

IsCurrentMonth = IF(MONTH([Date]) = MONTH(TODAY()),1,0)

Then just filter the page with IsCurrentMonth set to equal 0 only.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks smoupre!

 

Since I have multiple years worth of data, I had to make one small edition so it ended up looking like:

 

IF(MONTH([Date]) = MONTH(TODAY()) && YEAR([Date]) = YEAR(TODAY()), 1, 0)

 

But otherwise your formula worked like a charm. 

 

Thanks again.

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.