How to use the synchronous slicer with drillthrough to dynamically calculate the sum of Qty

[v-jingzhan-msft]

Scenario:

In this post, we will use Sync slicer with the drill-through feature to dynamically calculate data over a period of time.

Let's say we have a sales report with multiple pages: one for overall sales, another for product categories, and a third for regional sales. On the page for overall sales, we want to analyze sales data for a specific month, so set a slicer for the month name (for example, January, February, and so on). On other pages we want to see some more sales data by drilling through from the overall sales page. When drilling through from the overall sales page to the product category or regional sales page, we hope to see the sales data between the drill point date and the slicer date, instead of only the data on the drill point date by default. To deal with that, using the sync slicer to apply the Month slicer to all pages may be an option. Let’s go to the following sections for details.

Expected Result:

I have some sample data as below.

I have a line chart and a slicer on the page 1 (page for overall sales). This line chart has the date column as the X-axis and QTY as the Y-axis. The slicer field is the year and month. I can select months in the slicer and see corresponding QTY data displayed in the line chart.

Next, drill through at a date point (e.g. 4/1/2024) in the line chart to the page 2:

I set up a table visual on the page 2. In addition to displaying the sales QTY of the drill through date (e.g. 45 in below image), I also want to have the sum of QTYs between the smallest date in the slicer-selected month on the previous page and the date of the drill through, e.g. 155 in the image (155=30+40+15+25+45).

How:

Create a date table based on the date columns of the original table, and then use the date columns of this date table in the slicer to create a synchronized slicer.

1. Use the following DAX expression to create a Calendar table:

Calendar table = CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )

2. Create a relationship between two tables:

3. Use the Date column of the new table in the page1 report page:

4. Create a same slicer in page2 and enable the synchronized slicer feature:

5. Set the drill through field to Qty:

6. Create two measures to get the drill through date and the minimum date in the year-month slicer's selected options:

DrillDay = CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table'[Date] ) )

minday = CALCULATE ( MIN ( 'Calendar table'[Date] ), ALLSELECTED ( 'Calendar table'[Date] ) )

7. Create a measure using the following DAX expression to calculate the sum of QTY between two dates

Qtysum =
VAR _slicerday = [minday]
VAR _drillday = [DrillDay]
RETURN
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Qty] ),
            'Table'[Date] >= _slicerday
                && 'Table'[Date] <= _drillday
        )
    )

8. Create a table visual object with drill through fields and Qtysum measure:

9. To hide the slicer of the drill through page:

Summary:

Drill through allows you to navigate from one page to another while preserving context, while synchronous slicers ensure consistent filtering across pages. We can use them at the same time to dynamically calculate data over time.

Author: Jianpeng Li

Reviewer: Ula and Kerry

[Blog]demo.pbix