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

[ManonNL]

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

[mark_endicott · Accepted solution]

@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.

[DemoFour]

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. 

[ManonNL]

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

[FreemanZ]

hi @ManonNL ,

you may add a calculated column like:

before the end of this month =

IF (

   [Date]<=EOMONTH(TODAY(), 0),

   "Yes", "No"

)

[ManonNL]

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?

[FreemanZ · Accepted solution]

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.

[mark_endicott · Accepted solution]

@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.