Automatically add months based on thrill through filter

[Yubo]

Hello,

I have two pages:
Page1 - calculated based on YTD formula, if picking up Jun (Fiscal month slicer), it means calculated from April to June.

Page2 - list detail records based on the date
Problem: when drill through from page 1 which select June to page2, page2 only displays the records in June, we need to include April, May and June.
It works if select all the months manually, but the clients ask for including those months automatically when drill through to page2.
Anyway to pass around this?
Many thanks! 😊

[Icey]

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.

[Yubo]

Hi Icey,

 Wow, that's exactually what I am looking for.  Many thanks 🙏

The difficult parts are:

1. I need to use  YTD calulations (not only 3 months) in the first page.

Here is one of the formula:

ActYTD = CALCULATE([Actuals],DATESYTD('Calendar'[Date],"31/3"))

The Date will be showing up in the page2.

Is it possible to change my formula into your "Last 3 months sum "->not only 3 month=YTD and "Measure"

2. I have a calendar table based on the date will show in the page2,  the calendar need to have relationship among other. please see the attachment. is it will be a problem?

I believe it will work using your ideas, but I don't know how to change your "Last 3 months sum " and "Measure".

YuBo

[Icey · Accepted solution]

Hi @Yubo ,

Please check if the workaround below is working.

1. Create another Calendar Table without relationship in your scenario.

2. Create Year and Month slicers from the Calendar Table without relationship.

3. Change your YTD measure like so.

ActYTD 2 = 
VAR SelectedYear =
    SELECTEDVALUE ( 'Calendar without relationship'[Year] )
VAR SelectedMonth =
    SELECTEDVALUE ( 'Calendar without relationship'[Month] )
VAR StartDate =
    IF (
        SelectedMonth IN { 1, 2, 3 },
        DATE ( SelectedYear - 1, 4, 1 ),
        DATE ( SelectedYear, 4, 1 )
    )
VAR EndDate =
    DATE ( SelectedYear, SelectedMonth + 1, 1 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Profit] ),
        FILTER (
            ALL ( 'Calendar with relationship' ),
            'Calendar with relationship'[Date] >= StartDate
                && 'Calendar with relationship'[Date] < EndDate
        )
    )

4. Just put the "Measure" in the attached PBIX file in all visuals you want to show 3 months.

Measure = 
VAR CurrentDate =
    MAX ( 'Table'[Date] )
VAR SelectedYear =
    SELECTEDVALUE ( 'Calendar without relationship'[Year] )
VAR SelectedMonth =
    SELECTEDVALUE ( 'Calendar without relationship'[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
    )
)

Then you can get this:

For more 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.

[Yubo]

Hi Icey,

It works perfectly if only two levels of drill through -page1 & page2.

But I have 4 levels of drill through-page1,2,3 &4, the first 3 levels have fiscal Year and month filters, the last page is details.

Tried to make a connotation between the filters.

if in page3,  I put 4 filters (hide all of them) - FiscalYear,  FiscalMonth (the values carried from level2), Year ,Month in without relationship calendar. In the measure (ActYTD), to pass the values from FiscalYear/fiscalmonth to Year/month, then use the ways you did. The formula I tried below is not working, not know how to write the code.

 Do you think it will be a way? Or any ideas for 4 levels drill through?

ActYTD 2 =

VAR SelectedYear =

IF (SELECTEDVALUE ( 'Calendar'[FiscalYear])<>0,

    SELECTEDVALUE ( 'Calendar without relationship'[Year] )=SELECTEDVALUE( 'Calendar'[FiscalYear] ))

             //SELECTEDVALUE ( 'Calendar'[FiscalYear])

VAR SelectedMonth =

if (SELECTEDVALUE ( 'Calendar'[FiscalMonth])<>0,

    SELECTEDVALUE ( 'Calendar without relationship'[Month] )=SELECTEDVALUE ( 'Calendar'[FiscalMonth]))

      //SELECTEDVALUE ( 'Calendar'[FiscalMonth])

VAR StartDate =

    IF (

        SelectedMonth IN { 1, 2, 3 },

        DATE ( SelectedYear - 1, 4, 1 ),

        DATE ( SelectedYear, 4, 1 )

    )

VAR EndDate =

    DATE ( SelectedYear, SelectedMonth + 1, 1 )

RETURN

    CALCULATE([Actuals],DATESYTD('Calendar'[Date],"31/3"),

   // VALUE([ActYTD]),

        FILTER (

            ALL ( GL ),

            GL[GL Date] >= StartDate

                && GL[GL Date]< EndDate

        )

    )

Thank you so much Icey!! 🙏

YuBo

[Yubo]

Hi Icey,

Good News - It worked like a charm.  😱

Many thanks for your supports!!

All the best,

YuBo