Two Period Slicers filtering data

[PowerBI-Newbie]

Hi,

I'm looking to create two slicers, where the user selects Start Period and End Period (see below):

At the moment they're having to use a single slicer and multi-select the periods that way (see above picture) but I can see this causing issues long-term as the list of periods increases. I know there's the ability to use one date slicer that gives the user the ability to select the date range but the brief is that Date isn't to be used for filtering but rather Period, and when using Period the range option isn't available hence the need for two period slicers.

Another requirement is for the second slicer to be filtered based on the selection from the first slicer such that an earlier Period can't be selected on the 2nd slicer e.g. if the user selects Period 4 in slicer 1 and Period 3 in slicer 2 - this shouldn't happen so when the user selects Period 4 in slicer 1 then they can only see Period 4, Period 5, etc. If there's a way for the user to not be able to select from slicer 2 until a selection has been made from slicer 1 then that would resolve any issues in the event that they make a selection from slicer 2 first before slicer 1.

How can I implement the above? Any help is greatly appreciated.

[danextian]

hi @PowerBI-Newbie 

You will need to use two disconnected dates table - on for period 1 and another for period2. Using a related table will show just what is selected. And then refererence those tables to filter an aggregation.

I'm using these measures below in the screenshot

Value within Periods = 
CALCULATE (
    [Sum of Value],
    KEEPFILTERS (
        DatesTable[Date] >= MIN ( Period1[Date] )
            && DatesTable[Date] <= MAX ( Period2[Date] )
    )
)


Filter from Period1 = 
--as visual filter for Period2, not blank
CALCULATE (
    COUNTROWS ( Period2 ),
    KEEPFILTERS ( Period2[Date] >= MIN ( Period1[Date] ) )
)

Refer to the attached pbix for the details.

[PowerBI-Newbie]

Hi @danextian , thank you for your response.

The periods come in the following format YY-YY:Period - 23-24:09 for example is Period 9 in the financial year 2023-2024. See below sample list:

Whereas yours is in Month-Year format. How do I do what you did for my requirements in terms of periods?

[danextian]

What month does your FY begin?

[PowerBI-Newbie]

It begins in April but there are 13 periods in a financial year.

[danextian]

How are the dates distributed accross periods then? Please provide a sample data or sample pbix.

[PowerBI-Newbie]

Here's a sample data:

[danextian]

Not an image please.

[PowerBI-Newbie]

Period No. Start Date End Date
Period 1 01/04/2023 29/04/2023
Period 2 30/04/2023 27/05/2023
Period 3 28/05/2023 24/06/2023
Period 4 25/06/2023 22/07/2023
Period 5 23/07/2023 19/08/2023
Period 6 20/08/2023 16/09/2023
Period 7 17/09/2023 14/10/2023
Period 8 15/10/2023 11/11/2023
Period 9 12/11/2023 09/12/2023
Period 10 10/12/2023 06/01/2024
Period 11 07/01/2024 03/02/2024
Period 12 04/02/2024 02/03/2024
Period 13 03/03/2024 31/03/2024
Period 1 01/04/2024 27/04/2024
Period 2 28/04/2024 25/05/2024
Period 3 26/05/2024 22/06/2024
Period 4 23/06/2024 20/07/2024
Period 5 21/07/2024 17/08/2024
Period 6 18/08/2024 14/09/2024
Period 7 15/09/2024 12/10/2024
Period 8 13/10/2024 09/11/2024
Period 9 10/11/2024 07/12/2024
Period 10 08/12/2024 04/01/2025
Period 11 05/01/2025 01/02/2025
Period 12 02/02/2025 01/03/2025
Period 13 02/03/2025 31/03/2025

[DataNinja777]

Hi @PowerBI-Newbie ,

To create two slicers (Start Period and End Period) with the periods formatted as "23-24.01," "23-24.02," and so on, where the second slicer dynamically updates based on the selection in the first slicer, follow these steps:

First, ensure you have a table, which we’ll call PeriodTable, that contains all the periods in the required format (e.g., 23-24.01, 23-24.02). This table should also include a numeric column PeriodOrder that assigns an order to each period (e.g., 1 for 23-24.01, 2 for 23-24.02, etc.). This numeric column will help in determining the filtering logic.

In your Power BI model, create two measures. The first measure, SelectedStartPeriod, captures the order of the Start Period selected by the user. Use the formula:

SelectedStartPeriod = MAX(PeriodTable[PeriodOrder])

Next, create a second measure, EndPeriodFilter, which will filter the End Period slicer to show only periods greater than or equal to the Start Period. Use the following formula:

EndPeriodFilter = 
IF(
    ISFILTERED('PeriodTable'[PeriodOrder]) &&
    MAX('PeriodTable'[PeriodOrder]) >= [SelectedStartPeriod] &&
    NOT ISBLANK([SelectedStartPeriod]),
    1,
    0
)

Add both slicers to your Power BI report. For the Start Period slicer, drag the Period column from the PeriodTable and set it up as a dropdown or list slicer. For the End Period slicer, drag the Period column again, and apply the EndPeriodFilter measure as a visual-level filter, ensuring it only shows values where the measure equals 1.

This setup ensures that the End Period slicer remains blank until a Start Period is selected, and it dynamically updates to only show periods that are equal to or later than the selected Start Period. For example, if "23-24.05" is selected in the Start Period slicer, the End Period slicer will only show "23-24.05" through "23-24.12."

Finally, test the report to ensure that selecting a Start Period filters the End Period slicer correctly and that the End Period slicer remains non-functional until a Start Period is chosen. This approach is robust and accommodates your specific period format seamlessly.

Best regards,

[PowerBI-Newbie]

Hi @DataNinja777 , thank you for your response.

Unfortunately that doesn't work for me as I get the following:

It doesn't show anything greater in slicer 2 than what was selected in slicer 1. Are you able to send through pbix file please?

Furthermore, and once this is resolved, how do I get this to filter the graphs that I have in my report?