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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ManonNL
Frequent Visitor

How to filter on all of the past till end of this month?

Hi guys,

 

I hope someone can help me, since I'm not the best (yet..) in combining formulas.

 

I just need to filter a date column in a visual on all dates untill end of the current month. It is not possible to use a simple filter option, since the most accurate option is to choose recent months untill today, but then I don't have all of the month. So, I need to write a measure/calculated column for it.

 

It doesn't matter if it is a calculated column with yes/no or a measure with a certain value that I can use in my visual filtering. As long as I can filter my visual based on the past untill end of this month.

 

I look forward to responses.

Kind regards,

Manon

2 ACCEPTED SOLUTIONS

@ManonNL - You can fix the blank issue easily with this:

 

SWITCH( TRUE(),
ISBLANK( [Date] ), "No",
[Date]<=EOMONTH(TODAY(), 0),
   "Yes", "No"
)

 

If this works, please accept as the solution, it helps with visibility for those with the same problem.

View solution in original post

hi @ManonNL ,

 

then it should be like:

before the end of this month = 
IF (
    OR(
        [Date]>EOMONTH(TODAY(), 0),
        ISBLANK([Date])
    )
   "No", "Yes"
)

 

what mark_endicott proposed shall also work.

View solution in original post

6 REPLIES 6
DemoFour
Responsive Resident
Responsive Resident

Hay @ManonNL 

If you have a date table created in the Query Editor with M Code then you can add a column in to the table like this: 

 

= if Date.IsInCurrentMonth([Date]) then "Current Month" 
else if Date.From([Date]) < Date.From(DateTime.LocalNow()) then "Previous Month" 
else "Future Month"

 

 

 Then use this field in date table as a filter on your visual. 

My date column was a calculated column. But thanks for your response, I'm sure this will come in handy for other cases.

FreemanZ
Super User
Super User

hi @ManonNL ,

 

you may add a calculated column like:

before the end of this month =
 
IF (
   [Date]<=EOMONTH(TODAY(), 0),
   "Yes", "No"
)

Thank you so much! It almost helps entirely, but blank values give "Yes" now. Do you know what I can add to make blank values No?

hi @ManonNL ,

 

then it should be like:

before the end of this month = 
IF (
    OR(
        [Date]>EOMONTH(TODAY(), 0),
        ISBLANK([Date])
    )
   "No", "Yes"
)

 

what mark_endicott proposed shall also work.

@ManonNL - You can fix the blank issue easily with this:

 

SWITCH( TRUE(),
ISBLANK( [Date] ), "No",
[Date]<=EOMONTH(TODAY(), 0),
   "Yes", "No"
)

 

If this works, please accept as the solution, it helps with visibility for those with the same problem.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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