Forum Discussion

jgrima's avatar
jgrima
New Member
1 year ago
Solved

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,...
  • bhanu_gautam's avatar
    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,
    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.