Date Slicer to impact measure but not X Axis

[NathanSaber]

Hi,

I want to show the rolling Year To Date amount on a line graph with the Month as the X Axis. I have a Month slicer on the page, and I want the line (YTD Measure) on the graph to stop for months after this slicer selection (so be blank for these months). So that the X Axis shows all months, I have made the Month slicer not interact with the Line Graph visual (using Edit interactions). However this means that my measure below doesn't show blanks for the months after the slicer selection. If I instead let the Month slicer interact with the Line Graph visual, it means my X Axis only displays the month selected in the slicer. Any solutions?

I've noted the details of the Slicer and the YTD Measure below. Noting too that I have a 'date table' and a 'transactions table' which are connected.

Slicer: 'Date'[MonthYear]

YTD Measure = VAR _1 =

    -TOTALYTD(SUM(Transactions[Amount]),'Date'[Date],"30-6"

    )

VAR _sd =

    MAX('Date'[Date])

VAR _ss =

    MAX('Date'[MonthYear])

RETURN

    IF( _ss <= _sd, BLANK(), _1 )

[v-csrikanth]

Hi @NathanSaber 
Sorry for the late response.

You're absolutely right in identifying the core issue: when you disable slicer interaction with the visual to preserve all months on the X-axis, the slicer’s selection doesn't affect the measure's evaluation context — which is why the MAX('Date'[Date]) returns the current row context date instead of the slicer-filtered one.

To solve this, we need to decouple the slicer logic from the visual context, allowing the measure to respect slicer selection even when slicer interaction is turned off.

Here’s a revised approach using a disconnected date table for the slicer:

1. Create a disconnected date table using this DAX expression:
   SlicerDate = DISTINCT('Date'[MonthYear])
2. Use this new table 'SlicerDate' as the source for your MonthYear slicer on the report page.
3. Replace your YTD measure with the following logic:

YTD Measure =

VAR SelectedMonth = MAX('SlicerDate'[MonthYear])

VAR SelectedDate =

    CALCULATE(

        MAX('Date'[Date]),

        FILTER('Date', 'Date'[MonthYear] = SelectedMonth)

    )

VAR CurrentDate = MAX('Date'[Date])

VAR YTDValue =

    TOTALYTD(

        SUM('Transactions'[Amount]),

        'Date'[Date],

        "30-6"

    )

RETURN

    IF(CurrentDate <= SelectedDate, YTDValue, BLANK())

This setup allows your line graph to display all months on the X-axis while only showing YTD values up to the selected slicer month.

If the above information helps you, please give us a Kudos and marked the Accept as a solution.

Best Regards,
Community Support Team _ C Srikanth.

[grazitti_sapna]

Hi @NathanSaber , 

Please try using: 

YTD Measure =
VAR _MaxSelectedDate =
MAX( 'Date'[Date] ) -- Get the max date from the slicer selection
VAR _CurrentDate =
MAX( 'Date'[Date] ) -- Get the current date on the X-axis
VAR _YTDValue =
TOTALYTD( SUM( Transactions[Amount] ), 'Date'[Date], "30-6" ) -- Calculate YTD

RETURN
IF( _CurrentDate <= _MaxSelectedDate, _YTDValue, BLANK() ) -- Blank out values beyond slicer selection

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

🔗 Curious to explore more? [Discover here].

Let’s keep building smarter solutions together!

[NathanSaber]

Thanks for the reply. I think the issue with this is that I have had to make the slicer have no interaction with the line graph visual, or only the month selected in the slicer will show on the X Axis. Therefore the Max Selected Date and the Current Date don't work. Also I think in the formula, both the Max Selected Date and the Current Date are the same?

[pankajnamekar25]

Can you please try this

YTD Measure =
VAR _LastSelectedMonth = MAX( 'Date'[Date] ) 
RETURN
IF(
MAX( 'Date'[Date] ) > _LastSelectedMonth,
BLANK(),
TOTALYTD( SUM( Transactions[Amount] ), 'Date'[Date], "30-6" )
)