Forum Discussion
Helper IIFiltering categorical X-axis based on dynamic date
Hi everyone,
I have a dashboard where I compare model predictions to actual planned data over a series of dates. Both predictions and actuals overlap for a certain range of dates, but not necessarily the entire date span available in the data.
On my X-axis, I display week numbers (as categorical values), which prevents me from using a simple maximum value formula to limit the date range displayed.
What I want:
- To display only the weeks where both predictions and actuals are available (i.e., the overlapping dates).
- The displayed date range should update dynamically based on the selected prediction (since we have multiple prediction runs).
My challenge:
Since the X-axis is categorical (week numbers), I can’t set a dynamic maximum/minimum value easily.
How can I configure my dashboard/chart to show only the overlapping weeks for the selected prediction and actuals?
Thanks in advance.
Hey GijsBI,
I had to deal with the same issue once. The trick is to force your X-axis to only pick up the common weeks from both tables.
- Make sure you’ve got a proper Week/Calendar table that links to Actuals and Predictions.
- Create a small table of just the overlapping weeks:
OverlapWeeks =
INTERSECT (
SELECTCOLUMNS ( Actuals, "Week", Actuals[Week] ),
SELECTCOLUMNS ( Predictions, "Week", Predictions[Week] )
)- Put OverlapWeeks[Week] on the X-axis instead of the week column from your fact tables.
- Now when you switch Prediction runs, the axis adjusts and only shows the common range.
That’s the simplest way I found to keep the chart clean and relevant.
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer
4 Replies
- jaineshp
Memorable Member
Hey GijsBI,
I had to deal with the same issue once. The trick is to force your X-axis to only pick up the common weeks from both tables.
- Make sure you’ve got a proper Week/Calendar table that links to Actuals and Predictions.
- Create a small table of just the overlapping weeks:
OverlapWeeks =
INTERSECT (
SELECTCOLUMNS ( Actuals, "Week", Actuals[Week] ),
SELECTCOLUMNS ( Predictions, "Week", Predictions[Week] )
)- Put OverlapWeeks[Week] on the X-axis instead of the week column from your fact tables.
- Now when you switch Prediction runs, the axis adjusts and only shows the common range.
That’s the simplest way I found to keep the chart clean and relevant.
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer
GijsBIHi Jainesh,
I think this is the appropriate solution. It does not work for me as expected, but that is because of my data model I think. I'll have to re-do the data model and then this would hopefully work.
- GijsBI
Hi jaineshp ,
Maybe you can help me with the last part of this issue. The logic you mentioned seems to work partly. The predictions table contains both run dates and prediction dates. Currently, the solution checks overlapping dates/weeks across all run dates and their corresponding predictions.
However, what I need is to filter the predictions data so that it only shows prediction dates associated with the run date selected in the dashboard. I tried using SELECTEDVALUE and FILTER, but I haven’t been able to create a dynamic subset based on the slicer value.
Do you know if there’s a way to achieve this?
Thanks in advance for your help!
rohit1991Super User
Hi GijsBI
You can handle this by creating a filter measure that only shows the overlapping weeks. The idea is to find the first and last week where both Actuals and Predictions have data, then only keep those.
Steps:
- Create measures for MinWeek and MaxWeek (weeks where both values exist).
- Create a flag like ShowWeek = IF(WeekNumber >= MinWeek && WeekNumber <= MaxWeek, 1, 0).
- Put ShowWeek = 1 as a visual filter on your chart.
Now your X-axis will only display the common weeks for Actuals and the selected Prediction run. If you have multiple years, build a YearWeek column to keep the order correct.