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

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 !!