Forum Discussion
Matrix visual showing only selected date for current month instead of all dates up to 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).
Super UserHi 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).