Forum Discussion

Jarek_Warsaw's avatar
Jarek_Warsaw
Frequent Visitor
1 year ago

Trouble Configuring Date Slicer Interaction for Chart Tooltip

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!

Need Help

6 Replies

  • Jarek_Warsaw's avatar
    Jarek_Warsaw
    Frequent Visitor
    1 year ago

    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's avatar
      audreygerred
      Super User
      1 year ago

      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's avatar
        Jarek_Warsaw
        Frequent Visitor
        1 year ago

        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's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Jarek_Warsaw
      Frequent Visitor
      1 year ago

      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?