Forum Discussion

Elango's avatar
Elango
New Member
1 year ago
Solved

Power BI

Hello everyone,

I have a table that contains Start Date and End Date, and I’d like to display the years between these dates in a slicer. I also have a separate slicer for Customer, and based on the selected customer, I want to dynamically show the corresponding years in the Year slicer.

Example:

  • Customer A: Start Date: 1/1/2022, End Date: 2/2/2024
    Expected Years in Slicer: 2022, 2023, 2024
  • Customer B: Start Date: 1/1/2023, End Date: 2/2/2026
    Expected Years in Slicer: 2023, 2024, 2025, 2026

Is there a solution for this?

Thank you in advance!

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from danextian and saud968, please allow me to provide another insight.
    Hi Elango ,

    I am not sure how your source data table is designed, below is my sample.
    Create a table for creating a year slicer with years derived from the years between Start Date and End Date.

    Year_Slicer = 
VAR _calendar =
    CALENDAR ( MIN ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ) )
RETURN
    DISTINCT ( SELECTCOLUMNS ( _calendar, "Year", YEAR ( [Date] ) ) )

     

    Creates a measure to return a range of years for the selected user

    Year range = 
IF (
    SELECTEDVALUE ( 'Table'[Customer] ) <> BLANK (),
    FILTER (
        'Year_Slicer',
        'Year_Slicer'[Year] >= YEAR ( MIN ( 'Table'[Start Date] ) )
            && 'Year_Slicer'[Year] <= YEAR ( MAX ( 'Table'[End Date] ) )
    ),
    1
)

    Add this measure to the visual level filter of the year slicer.


    The final result is as follows, hopefully it will meet your needs.


    Please see tha attached pbix for reference.

    Best Regards,
    Dengliang Li

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

     

     

5 Replies

  • saud968's avatar
    saud968
    Memorable Member
    1 year ago

    Create a Date Table:
    Go to Modeling > New Table and create a date table that covers the range of years you need:
    DateTable =
    CALENDAR(
    DATE(2022, 1, 1),
    DATE(2026, 12, 31)
    )

    Create a Calculated Table for Years:
    Create a new table that will dynamically generate the years based on the selected customer:
    YearsTable =
    DISTINCT(
    SELECTCOLUMNS(
    FILTER(
    DateTable,
    DateTable[Date] >= MIN('YourTable'[Start Date]) &&
    DateTable[Date] <= MAX('YourTable'[End Date])
    ),
    "Year", YEAR(DateTable[Date])
    )
    )

    Create Relationships:
    Ensure you have relationships set up between your main table and the DateTable.
    Create Slicers:
    Add a slicer for Customer from your main table.
    Add a slicer for Year from the YearsTable.
    Sync Slicers:
    Make sure the slicers are synced so that selecting a customer updates the years slicer dynamically.

    Best Regards
    Saud Ansari
    If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!



    • Elango's avatar
      Elango
      New Member
      1 year ago

      Hi saud968 !
      Thanks for your response.As per your instruction I have created two table. Can you explain type of relationship between main table and Datetable?

      Thanks in Advance!!

      • saud968's avatar
        saud968
        Memorable Member
        1 year ago

        The relationship between your main table and the DateTable should be a many-to-one relationship. Here’s how you can set it up:

        Open the Model View:
        Go to the Model view in Power BI.
        Create the Relationship:
        Drag the Start Date and End Date fields from your main table to the Date field in the DateTable.
        This will create two relationships:
        One between Start Date in your main table and Date in the DateTable.
        Another between End Date in your main table and Date in the DateTable.
        Set the Relationship Type:
        Ensure both relationships are set to many-to-one (many dates in the main table to one date in the DateTable).
        Set the Cross filter direction to Single to ensure the filtering works correctly.
        Activate the Relationship:
        You can only have one active relationship between two tables at a time. Typically, you would set the relationship between Start Date and Date as active.
        The relationship between End Date and Date can be inactive, but you can use it in DAX calculations if needed.
        This setup ensures that your DateTable can filter your main table based on the selected dates, allowing you to dynamically display the years in the slicer.

        Best Regards
        Saud Ansari
        If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Elango 

     

    You can add a custom column in Power Query with this formula.

    {Date.Year([Start])..Date.Year([End])}

    This will create a list of years from based on the start and end dates. And then you can expand the newly created column into new rows.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from danextian and saud968, please allow me to provide another insight.
    Hi Elango ,

    I am not sure how your source data table is designed, below is my sample.
    Create a table for creating a year slicer with years derived from the years between Start Date and End Date.

    Year_Slicer = 
VAR _calendar =
    CALENDAR ( MIN ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ) )
RETURN
    DISTINCT ( SELECTCOLUMNS ( _calendar, "Year", YEAR ( [Date] ) ) )

     

    Creates a measure to return a range of years for the selected user

    Year range = 
IF (
    SELECTEDVALUE ( 'Table'[Customer] ) <> BLANK (),
    FILTER (
        'Year_Slicer',
        'Year_Slicer'[Year] >= YEAR ( MIN ( 'Table'[Start Date] ) )
            && 'Year_Slicer'[Year] <= YEAR ( MAX ( 'Table'[End Date] ) )
    ),
    1
)

    Add this measure to the visual level filter of the year slicer.


    The final result is as follows, hopefully it will meet your needs.


    Please see tha attached pbix for reference.

    Best Regards,
    Dengliang Li

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