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