Forum Discussion
Matrix visual showing only selected date for current month instead of all dates up to selected date
Hi everyone,
I’m facing an issue with my Power BI report related to date filtering.
In my dataset, the date column contains end-of-month dates for all previous months, but for the current month, it contains daily dates (e.g., 1 Oct, 2 Oct, 3 Oct… etc.).
Here’s the behavior I’m seeing:
- When I select a date from previous months (like 30 Sep), my matrix visual correctly shows the data for that month.
- But when I select a date from the current month (for example, 15 Oct), the matrix shows data only for that single date, instead of showing all the daily production data from 1 Oct to 15 Oct.
My requirement : When I select 15 Oct, the matrix should display data for all dates from 1 Oct to 15 Oct.
Can someone please guide me on how to achieve this behavior in Power BI?
Do I need to use a disconnected date slicer or a specific DAX measure to make the matrix show all dates up to the selected date?
Use a standard date table with continuous dates. Create this measure:
Production to Date =
CALCULATE(
SUM(Production[Value]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date]) &&
'Date'[Date] >= EOMONTH(MAX('Date'[Date]), -1) + 1
)
)Use this measure in your matrix and slice by your date table, not the production date column.
Hi Mohan_Vanku
This issue happens because your SelectedDate table has:
- End-of-month dates for older months, and
- Daily dates for the current month.
So, when you select a date like 15 Oct, Power BI shows only that day’s data.
To fix this:
- Create a new DAX measure:
ShowTillSelectedDate =
VAR _maxDate = MAX('cmpst_calendar'[SelectedDate])
VAR _monthStart = STARTOFMONTH('cmpst_calendar'[SelectedDate])
RETURN
IF(
'cmpst_calendar'[SelectedDate] >= _monthStart &&
'cmpst_calendar'[SelectedDate] <= _maxDate,
1
)2.Add this measure to your visual-level filters.
3.Set the filter to ShowTillSelectedDate = 1.
4.Use the date from your calendar table (not production date).
9 Replies
- bhanu_gautam
Super User
Mohan_Vanku , Try using
Create a Disconnected Date Table
Create a new date table that covers the full range of dates you want to allow for selection (e.g., all possible daily dates).
Do NOT create a relationship between this table and your fact table.DAX
SelectedDate = MAX('DisconnectedDate'[Date])ShowData =
IF(
MAX('FactTable'[Date]) <= [SelectedDate],
SUM('FactTable'[Value]), // or your aggregation
BLANK()
)Use this in matrix
rohit1991Hi Mohan_Vanku
This happens because your current month has daily dates, so when you pick a single date (like 15 Oct), Power BI only shows that one day’s data. You need to tell Power BI to include all days from the start of the month up to the date you selected.
Try this simple DAX measure:
ShowTillSelectedDate = VAR _maxDate = MAX('Date'[Date]) VAR _monthStart = STARTOFMONTH('Date'[Date]) RETURN IF( 'Date'[Date] >= _monthStart && 'Date'[Date] <= _maxDate, 1 )Now, add this measure to your visual filter and set it to ShowTillSelectedDate = 1.
This will make your matrix display data from 1 Oct to 15 Oct when you select 15 Oct and for past months, it will still show the full month as usual.
Thanks for the quick response, I tried this measure but it is still not working Rohit.
Here we have slicer dates(selectdate) and matrix visual dates coming from same table (calendar).
When I select date from previous months it is showing all the dates for that particular month.
If I select Oct 15 in current month date selection it is showing only single date in raw data.How do I achieve my solution when I have these conditions?
- rohit1991
Hi Mohan_Vanku
This issue happens because your SelectedDate table has:
- End-of-month dates for older months, and
- Daily dates for the current month.
So, when you select a date like 15 Oct, Power BI shows only that day’s data.
To fix this:
- Create a new DAX measure:
ShowTillSelectedDate =
VAR _maxDate = MAX('cmpst_calendar'[SelectedDate])
VAR _monthStart = STARTOFMONTH('cmpst_calendar'[SelectedDate])
RETURN
IF(
'cmpst_calendar'[SelectedDate] >= _monthStart &&
'cmpst_calendar'[SelectedDate] <= _maxDate,
1
)2.Add this measure to your visual-level filters.
3.Set the filter to ShowTillSelectedDate = 1.
4.Use the date from your calendar table (not production date).
- Kedar_Pande
Use a standard date table with continuous dates. Create this measure:
Production to Date =
CALCULATE(
SUM(Production[Value]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date]) &&
'Date'[Date] >= EOMONTH(MAX('Date'[Date]), -1) + 1
)
)Use this measure in your matrix and slice by your date table, not the production date column.
Kedar_Pande thanks for your reply.
Been a fan of your videos over the youtube.
Thanks for taking time and providing the suggestions.
In my dataset here,
I am using SelectedDate slicer which is coming from _cmpst_calender table.
As i said SelectedDate column contains - until previous month, it will have end of month date, but for the current month, it will have continous dates.Relationships -
_cmpst_calender[Date] <1-1> Calender[Date]Calender[Date] 1-M --> Agg_Transactions[EntryDate]
the production measure is present in Agg_transactions.
Please provide more details, like do i need to create another date table which is not connected to any other tables?
and create the measure as u mentioned?
v-sgandrathiCommunity Support
Hi Mohan_Vanku,
Currently, your month is set up with daily dates, so selecting a single day (like 15 Oct) filters the Calendar to just that day, and the matrix displays only that date. To have the matrix show all days from the start of the month up to the selected date, you can use a disconnected “SelectedDate” slicer, which isn’t linked to the model tables, and let your measure handle what’s visible. The slicer acts as a cutoff date (such as 15 Oct) without actually filtering the Calendar. Your measure then checks each date in the Calendar against two points: the start of the month and the cutoff date. If a date falls within this range, it’s shown; otherwise, it’s blank.
For previous months, the slicer uses the end-of-month value, so the matrix shows the full month. For the current month, it displays from the 1st to the selected day. This approach separates the concept of selection (cutoff) from filtering (Calendar), allowing the matrix to expand to the full month-to-date view instead of just a single day.
Thank you.- v-sgandrathi
Hi Mohan_Vanku,
Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.
Thank you.- v-sgandrathi
Hi Mohan_Vanku,
Just looping back one last time to check if everything's good on your end. Let me know if you need any final support happy to assist if anything’s still open.
Thank you.
