Automatically add months based on thrill through filter

Hi Yubo ,

In your scenario, drillthrough will pass all filters contained in the cell you selected to the second page.

You can try something like what I do in my simple example:

1. Create a Calendar table without any relationship among other tables.

Calendar = CALENDARAUTO()

2. Add Calculated columns.

Year = YEAR('Calendar'[Date])

Month = MONTH('Calendar'[Date])

3. Create slicers with Year and Month columns.

4. Create measure.

Last 3 months sum = 
VAR SelectedYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR SelectedMonth =
    SELECTEDVALUE ( 'Calendar'[Month] )
VAR SelectedFirstDayOfNextMonth =
    IF (
        SelectedMonth = 12,
        DATE ( SelectedYear + 1, 1, 1 ),
        DATE ( SelectedYear, SelectedMonth + 1, 1 )
    )
VAR SelectedFirstDayOfLastPirorLastMonth =
    IF (
        SelectedMonth = 1
            || SelectedMonth = 2,
        DATE ( SelectedYear - 1, SelectedMonth + 10, 1 ),
        DATE ( SelectedYear, SelectedMonth - 2, 1 )
    )
RETURN
    IF (
        SelectedYear = BLANK ()
            || SelectedMonth = BLANK (),
        SUM ( 'Table'[Profit] ),
        CALCULATE (
            SUM ( 'Table'[Profit] ),
            FILTER (
                'Table',
                'Table'[Date] < SelectedFirstDayOfNextMonth
                    && 'Table'[Date] >= SelectedFirstDayOfLastPirorLastMonth
            )
        )
    )

Measure = 
VAR CurrentDate =
    MAX ( 'Table'[Date] )
VAR SelectedYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR SelectedMonth =
    SELECTEDVALUE ( 'Calendar'[Month] )
VAR SelectedFirstDayOfNextMonth =
    IF (
        SelectedMonth = 12,
        DATE ( SelectedYear + 1, 1, 1 ),
        DATE ( SelectedYear, SelectedMonth + 1, 1 )
    )
VAR SelectedFirstDayOfLastPirorLastMonth =
    IF (
        SelectedMonth = 1
            || SelectedMonth = 2,
        DATE ( SelectedYear - 1, SelectedMonth + 10, 1 ),
        DATE ( SelectedYear, SelectedMonth - 2, 1 )
    )
RETURN
IF (
        SelectedYear = BLANK ()
            || SelectedMonth = BLANK (),
        1,
    IF (
        CurrentDate >= SelectedFirstDayOfLastPirorLastMonth
            && CurrentDate < SelectedFirstDayOfNextMonth,
        1
    )
)

5. Create visuals and Put Measure created in Step4 on "Filters on this visual" for all visuals on your drillthrough page.

Then, you can get this:

For details, please check the attached PBIX file.

Best Regards,

Icey

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