Dynamic Period Date Range on X-axis based on user slicer selections

[PowerBI-Newbie]

Hi,

I am trying to create a dynamic date range on the x-axis based on user slicer selections - there are to be two slicers, From Date and To Date and these will determine what the range for the x-axis should be, see example graph below:

The Start Period slicer selection is 22-23:08 and End Period slicer selection is 23-24:01 - both are in Year:Period format and both are single selection slicers. These form the date range for the x-axis.

The y-axis is simply a count of the number of records in the fact table.

As you can see the Year:Period after the End Period selection is showing on the graph - I can apply a filter to not show blanks but then I want to see blanks within the selected date range. Furthermore, the Start Period selection should start with the first date in the fact table and should not be greater than the End Period slicer selection. I can get the users to select the End Period first so that it can dynamically update the Start Period range.

I hope the request is clear, let me know if there are any queries.

[v-vpabbu]

Hi @PowerBI-Newbie,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[johnbasha33 · Accepted solution]

@PowerBI-Newbie 

### 🧩 Step-by-Step Solution

#### 1. ✅ **Create a Date/Period Dimension Table**

Your fact table likely uses a `Year:Period` string (like "22-23:08") for each record. Create a proper **Period table** with these columns:

| PeriodKey | Year | Period | YearPeriod |
|-----------|------|--------|------------|
| 2022-08 | 22-23| 08 | 22-23:08 |
| 2022-09 | 22-23| 09 | 22-23:09 |
| ... | ... | ... | ... |

> ⚠ Make sure to format `PeriodKey` as a real date, e.g., first day of the month (`2022-08-01`)—this lets you use it on the x-axis.

You can build this table in Power BI with `DAX` or Power Query.

Then, relate this table to your fact table using the date or period.

---

#### 2. ✅ **Create Two Slicers**
- Add `YearPeriod` from your Period table as a slicer.
- Set both to single selection:
- One for `Start Period`
- One for `End Period`

---

#### 3. ✅ **Create a Measure-Based Filter for the Chart Axis**

You don’t want to filter the data directly, because that would remove blanks (i.e., periods with no data). Instead, use a **DAX measure to return values only within the selected range**, and use that measure on the visual.

```DAX
ShowInRange =
VAR StartPeriod = SELECTEDVALUE('PeriodTable'[YearPeriod], "0000:00")
VAR EndPeriod = SELECTEDVALUE('PeriodTable (2)'[YearPeriod], "9999:99")
VAR CurrentPeriod = 'PeriodTable'[YearPeriod]
RETURN
IF(
CurrentPeriod >= StartPeriod &&
CurrentPeriod <= EndPeriod,
1,
0
)
```

> Replace `'PeriodTable (2)'` with your End Period slicer's disconnected table (see Step 5 below).

---

#### 4. ✅ **Build Your Chart**

- Use the **`PeriodKey`** (real date) from the Period table on the **x-axis** (formatted to show Year:Period).
- Use your **count measure** on the y-axis.
- Add the `ShowInRange` measure to the **visual filter pane**, set to show when `ShowInRange = 1`.

✅ This keeps all dates between the selected periods, **including blanks**, and removes everything else.

---

#### 5. 🔌 **(Optional but Cleaner) Use Disconnected Slicer Tables**

To avoid circular relationships or unexpected model behavior:

- Create **two disconnected tables** for `Start Period` and `End Period` slicers:

```DAX
StartPeriodSlicer = DISTINCT('PeriodTable'[YearPeriod])
EndPeriodSlicer = DISTINCT('PeriodTable'[YearPeriod])
```

These won’t be related to your model directly—just used in DAX measures like `ShowInRange`.

---

### ✅ Optional: Prevent Start Period > End Period

To guide users to avoid choosing an invalid range:
- Create a **validation measure** like:

```DAX
InvalidRange =
VAR Start = SELECTEDVALUE(StartPeriodSlicer[YearPeriod])
VAR End = SELECTEDVALUE(EndPeriodSlicer[YearPeriod])
RETURN
IF(Start > End, "⚠ Invalid range", BLANK())
```

Show this as a warning card or banner.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[PowerBI-Newbie]

Hi @johnbasha33 ,

Thank you for your response.

A couple of errors that I've ran into:

VAR CurrentPeriod = 'PeriodTable'[YearPeriod] - this causes an error as I may need to use MIN, MAX, etc.

ShowInRange measure doesn't seem to do anything.

Can you format date into periods so that I can have Periods 1 to 13 for a fiscal year instead of 12 months?

Do you happen to have a sample pbix file for me to have a look at please? Many thanks.

[v-vpabbu]

Hi @PowerBI-Newbie,

If you're using a function like MIN or MAX within your measure, this could be causing conflicts because YearPeriod is a text field in the PeriodTable, and you can't directly apply MIN/MAX to text values. Instead, convert the YearPeriod into a numeric value or date type so that you can apply MIN/MAX.

Thanks

Vinay Pabbu