PowerBI custom filter

[jgrima]

Hello, I'm trying to create an special filter for my table.

I have Year and Month columns, and I need an slicer that shows every year (2022, 2023, 2024...). When selecting a year, let's say 2024, I need to filter the table by every month of this year, and last year's December.

So, if I select 2024, I will see data for:

Dec 23  Jan 24  Feb 24  Mar 24  Apr 24  May 24  Jun 24  Jul 24  Aug 24  Sep 24  Oct 24  Nov 24  Dec 24

If I select 2023:
Dec 22  Jan 23  Feb 23  Mar 23  Apr 23  May 23  Jun 23  Jul 23  Aug 23  Sep 23  Oct 23  Nov 23  Dec 23

Does anybody knows how could I implement this?
Thank you.

[bhanu_gautam · Accepted solution]

@jgrima ,Ensure you have a date table that includes columns for Year, Month, and Date. If you don't have one, you can create it using DAX.

In your date table, add a custom column to identify the previous year's December for each year. You can use the following DAX formula:

PreviousDecember = IF(MONTH([Date]) = 12, YEAR([Date]), YEAR([Date]) - 1)

Add a slicer to your report and set it to filter by the Year column from your date table.
Create a Measure for Filtering:

Create a measure that will be used to filter the table based on the selected year and the previous year's December. You can use the following DAX formula:

FilterMeasure =
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
RETURN
IF(
YEAR('YourTable'[Date]) = SelectedYear ||
(YEAR('YourTable'[Date]) = SelectedYear - 1 && MONTH('YourTable'[Date]) = 12),
1,
0

Apply the Measure as a Visual Level Filter:
Add your table visual to the report.
Drag the FilterMeasure to the Filters pane for the table visual.
Set the filter to show only values where FilterMeasure is 1.