topic Re: How to filter on all of the past till end of this month? in DAX Commands and Tips

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

ManonNL — Wed, 30 Oct 2024 14:36:19 GMT

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

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

FreemanZ — Wed, 30 Oct 2024 14:42:02 GMT

hi ManonNL ,

you may add a calculated column like:

before the end of this month =

IF (

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

"Yes", "No"

)

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

ManonNL — Wed, 30 Oct 2024 14:50:49 GMT

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?

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

mark_endicott — Wed, 30 Oct 2024 15:04:47 GMT

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.

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

DemoFour — Wed, 30 Oct 2024 15:36:39 GMT

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.

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

FreemanZ — Thu, 31 Oct 2024 05:25:21 GMT

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.

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

ManonNL — Thu, 31 Oct 2024 10:24:34 GMT

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