Forum Discussion
PowerBI custom filter
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.
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,
0Apply 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.
1 Reply
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,
0Apply 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.