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 UserUse a standard date table with continuous dates. Create this measure:
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?