Matrix visual showing only selected date for current month instead of all dates up to selected date

[Mohan_Vanku]

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?

[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.

[Mohan_Vanku]

@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?

[rohit1991]

Hi @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.

[Mohan_Vanku]

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?

[bhanu_gautam]

@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