Help with filters

[gco]

Hi there,

I have a calendar table that has fulldate (year) and isPastMonth calculated column.

IsPastMonth = if Date.Month([#"FullDate"]) < Date.Month(DateTime.LocalNow()) then "Y" else "N"

How would i make the IsPastMonth show the past months of the year i selected?  I have a fulldate (year) slicer that currently show 2019 and 2020.

Do i have to create another measure of calculated column?

Thank you

Glen

[dax]

Hi @gco , 

I think you could use filter to achieve this. You could use slicer to filter year, then you could add IsPastMonth in visual or page filter, then choose "Y " in it which will show the past months of your selected year.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gco]

I have this on my report, but for some reason, it shows only January for both 2020 and 2019.  I wanted to be able to display the whole months of 2019, and only January for 2020.

Thanks

Glen

[gco · Accepted solution]

@dax @Anonymous ,

I was able to fix my problem by modifying the IsLastMonth calculated column as follows:

Old Value:

IsLastMonth = if Date.Month([#"FullDate"]) < (Date.Month(DateTime.LocalNow()) then "Y" else "N"

New Value:

IsLastMonth = if (Date.Month([#"FullDate"]) < (Date.Month(DateTime.LocalNow()))) or (Date.Year([#"FullDate"]) < (Date.Year(DateTime.LocalNow())))
then "Y" else "N"

This allowed the whole 2019 to be past month and January for 2020.

Thank you

Glen

[Anonymous]

Hi @gco ,

There should not be much else to do.

Depending on an approach you can either add a new column (Year) in the dataset and create a slicer from this column (and Y/N IsPastMonth filter on the visual containing "months") or you can go with grouping FullDates into years - I went with creating a group on the slicer visual binned to years.

The key, I guess, is to add the IsPastMonth filter to the either report, page or visual filters (just drag&drop in the filters section and set to "Y").

Kind regards,

JB