Two Period Slicers filtering data

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,

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.