Using two slicers to set a numeric range

Alec Zrike 0
Viewed 24

I have a column in my dataset which refers to the Quarter, set up as YYYYQ (ex: 20212 for Q2-2021) and I want to be able to use slicers to filter the data to a range of quarters. The Built-In range slicer is functional, but the slider includes quarters which do not exist (ex. 20216, there is no 6th Quarter in 2021).

Is there a way to use two separate dropdown slicers to set a range for the quarters, and calculate values within that range?

slicers powerbi
1 Answers
  1. Derive from your clumsy "quarter number" a First of Quarter date (calculated column)
First of Quarter = 
VAR STR = 
    CONVERT('Table'[Quarter], STRING)
RETURN
    DATE(LEFT(STR, 4), 1 + (RIGHT(STR) - 1) * 3, 1)

  1. Get more visuals from the market place and import Microsoft's Timeline Slicer into your report

  2. Add the Timeline Slicer to your page using the above First of Quarter date and select quarterly resolution.

The result will look something like this and will give you the slicer experience you are looking for:

enter image description here

userhiPt0
Related