Slicer between two values, but only for the values in dimension

[PBIuser73]

Hi.
I am simplifying the problem a bit more than it actually is. I have a table, Period. This has the following content (It is an integer. One value for each month, pluss 00 period. If necessary, I can convert it to text.):

Period:
202400
202401
202402
202403
202404
202405
202406
202407
202408
202409
202410
202411
202412
202500
202501
202502
202503
202504
202505
202506
202507
202508
202509
202510
202511
202512

I want to create a slicer that allows me to select Period From and Period To. When I use the slicer in Power BI, it creates all values from: 202400 to 202512. Not just the 26 values I have in my table.So it creates:202413, 202414, 202415 etc.

And these are periods that do not exist.
Someone that has a solution?

[ryan_mayu]

@PBIuser73 

I think that's by design and we don't have a direct solution for that. 

For me, the workaround  I i will use is to create a rank column and use that rank column to filter and display the start and end period beside the filter

pls see the attachment below

[PBIuser73]

Thank you for answer. Is it possible to have the period number inside the "range visual"?
I have many other filters in report, so it will take a lot of space with those extra visuals

[v-aatheeque]

Hi @PBIuser73 

If you have a "Date" table, you can create a custom column like:
PeriodLabel = "Period " & FORMAT('Date'[Date], "MM") & " - " & FORMAT('Date'[Date], "MMM")
This will generate values like: ,
Period 04 - Apr Period 05 - May
, etc.
Then use this column in a dropdown or list slicer this takes much less space than separate visuals and helps with filtering.

[PBIuser73]

Thanks for answer. I have a date table. But a field like this will be a text value. And i am not able to make between slicer? I need to have all transactions between two periods. 

[v-aatheeque]

Hi  @PBIuser73 

To effectively filter transactions between two periods in Power BI, I recommend the following approach :

1)Create a numeric PeriodNumber column:
PeriodNumber = MONTH('Date'[Date])

2)For fiscal years starting in April, use:
PeriodNumber = MONTH('Date'[Date]) - 3 + IF(MONTH('Date'[Date]) <= 3, 12, 0)

3)Create a unique PeriodKey for sorting and filtering:
PeriodKey = YEAR('Date'[Date]) * 100 + 'Date'[PeriodNumber]
(e.g., Apr 2024 becomes 202404)

4)Use PeriodKey in a slicer and set the slicer to “Between” This enables users to select a range like 202404 to 202408

5)All transaction rows within the selected period range will be filtered

6)To show user-friendly labels, create:
PeriodLabel = FORMAT('Date'[Date], "MMM YYYY")

7)Use PeriodLabel in visuals, tooltips, or cards for readability Avoid using text fields in “Between” slicers since Power BI only supports numeric or date fields and overcrowding the report page with too many visuals due to limited space

Hope this helps !!

[v-aatheeque]

Hi @PBIuser73 

Just checking back on the approach I shared earlier for filtering transactions between two periods using a custom PeriodKey in Power BI.

To recap, using a numeric key like PeriodKey (e.g., 202404 for April 2024) not only enables the use of the "Between" slicer functionality but also simplifies sorting and filtering across fiscal periods  especially if your fiscal year starts in April.

If you’ve had a chance to try this out and are facing any issues or need help adapting it to your specific model, feel free to share your current setup. I’ll be happy to assist further or troubleshoot any blockers.

And if it’s already working well, that’s great too  feel free to let us know so others in the community can benefit from your experience!

Looking forward to your update.

[MasonMA]

@PBIuser73 

Hi, The issue appears when the slicer is treated as a continuous numeric range, specifically/only in a “Between” slicer on a numeric column. as in below picture when i tried to replicate your issue, if i use 'Text' format or change the Slicer style to Dropdown/list, the slicer is treating values as categorical and you will not see the issue anymore. 

But if you prefer to use this 'Between' Style Slicer, you'd need to create 2 separate table like below and use them in 2 different slicers, one for 'Period From' , the other for 'Period To'.

PeriodFrom = DISTINCT(SELECTCOLUMNS('Table', "Period", 'Table'[PeriodText]))

PeriodTo = DISTINCT(SELECTCOLUMNS('Table', "Period", 'Table'[PeriodText]))

and create another DAX measure to filter your fact table with below DAX.

SelectedPeriodFrom = SELECTEDVALUE('PeriodFrom'[Period])
SelectedPeriodTo   = SELECTEDVALUE('PeriodTo'[Period])

Filtered Measure =
CALCULATE (
    [Measure],
    FILTER (
        FactTable,
        FactTable[Period] >= SelectedPeriodFrom &&
        FactTable[Period] <= SelectedPeriodTo
    )
)

But again, this can only be used in 2 separate dropdown/list slicers, one for 'Period From', the other one for 'Period To'.

If in your situation, i would simply change them to 'Text' format and use Dropdown/List style in Slicer. 

Hope it helps. 

[PBIuser73]

Hi. Thanks for answer. For this solution you have to make a measure in fact table for this? Because then i have to duplicate fields. The date i showed you were one of to "time" dimensions in the model. I have totaly 2. One "dim.date" and one "dim.period" (that is the one i showed you)