Power BI

[Elango]

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!

[Anonymous · Accepted solution]

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.

[danextian]

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.

[saud968]

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]

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]

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!