Forum Discussion
PowerBI custom filter
- 1 year ago
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.
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.