Forum Discussion

Yubo's avatar
Yubo
Helper I
6 years ago
Solved

Automatically add months based on thrill through filter

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! 😊

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

5 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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's avatar
      Yubo
      Helper I
      6 years ago

      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's avatar
        Icey
        Community Support
        6 years ago

        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.