Trouble Configuring Date Slicer Interaction for Chart Tooltip

[Jarek_Warsaw]

Hello Power BI Community,

I’ve been working on a report where I’m trying to display data for a selected monthly period along with a trend visualization. My goal is to show data from the beginning of the previous year up to the selected period using a chart within a tooltip.

What I’ve Tried So Far:

I created a measure to display the trend from the start of the previous year until the selected period, using the following DAX code:

CALCULATE(
[Sales_MTH],
REMOVEFILTERS(DICT_Calendar),
DATESBETWEEN(DICT_Calendar[Date], DATE(YEAR(MAX(DICT_Calendar[Date])) - 1, 1, 1), MAX(DICT_Calendar[Date]))
)

Initially, I was encountering an issue where the chart was only displaying a single data point for the selected period, regardless of what filters I applied.

Attempted Solution:

Following advice from a community member, I disabled the interaction between the date slicer and the chart. This resolved the issue of displaying only one point, and now the chart shows the full available range of data.

However, I would still like the slicer to influence the chart in a different way – ideally, I want to set the range from the beginning of the previous year up to the selected period in the slicer, rather than completely disconnecting the slicer from the chart.

My Questions:

1. Is there a way to reference the selected value in the slicer and use it to define a dynamic filtering range (e.g., from January 1st of the previous year to the selected date)?
2. How can I control the interaction between the date slicer and the tooltip chart more effectively to achieve this?

Thank you in advance for your suggestions!

[Jarek_Warsaw]

Hello everyone,

I am working on a report in Power BI where I present data for a selected monthly period. Additionally, I would like to include a trend visualization that shows data from the beginning of the previous year up to the selected period.

I created the following measure:

CALCULATE(
[Sales_MTH],
REMOVEFILTERS(DICT_Calendar),
DATESBETWEEN(DICT_Calendar[Date], DATE(YEAR(MAX(DICT_Calendar[Date])) - 1, 1, 1), MAX(DICT_Calendar[Date]))
)

I also tried using the ALL and ALLSELECTED functions to remove filters, but each time I got the same result: only a single data point for the selected period is displayed.

Does anyone have an idea what could be causing this issue and how I might fix or work around it?

Thank you in advance for any suggestions or advice!

Jarek

[audreygerred]

On your canvas, if you have the slicer for the date being selected, you can select that visual (the slicer) then go to the Format ribbon, select interactions and turn off teh interaction between your slicer and the chart.

[Jarek_Warsaw]

Thank you very much for your help. I feel like I’m getting closer to a solution. After turning off the interaction between the slicer and the chart, I was able to get a chart displaying data for the full available range.

However, I would like to ask if it's possible to reference the selected value in the slicer to set a different filtering range for the chart – for example, from the beginning of the previous year to the selected period.

Also, how can I configure the interaction between the date slicer and the chart in a tooltip?

Thanks again for your help!

[Anonymous]

Hi @Jarek_Warsaw ,

Based on the testing, please try the following methods:

1.Create the new date table.

Table 2 = CALENDAR(DATE(2023,1,1), DATE(2024,10,9))

2.Drag the new table 2 date column to the slicer visual.

3.Create the new measure to filter the date range.

SelectedDate = MAX('Table 2'[Date])

TrendMeasure = 
var _date = SELECTEDVALUE('Table'[Date])
RETURN
IF(_date >= DATE(YEAR([SelectedDate]) - 1, 1, 1) && _date <= [SelectedDate], 1, 0)

4.Drag the measures into the chart visual Filters pane.

5.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jarek_Warsaw]

Thanks a lot for your help.

As I understand your solution 'Table' is a fact table, and 'Table 2' is a dictionary table with calendar.
The problem is that my fact table is made from 2 sources:

SalesMTH =

VAR Sales_act = CALCULATE(

    SUM('Sales'[Value]),

    'Sales'[Attribute] = "direct")

VAR Sales_hist = CALCULATE(

    sum('ARCH_Clients'[Value]),

    'ARCH_Clients'[Table] = "sales")

RETURN

IF(MAX('DICT_Calendar'[Date]) >= DATE(2024, 1, 1),

    Sales_act,

    Sales_hist)

How to call a Date from 2 fact tables?

[Ritaf1983]

Hi @Jarek_Warsaw 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly