Forum Discussion

ianallen13V2's avatar
ianallen13V2
Regular Visitor
1 year ago
Solved

Dual Slicing

Hello, 

 

I have a request and I'm not sure if it is possible, but my company currently is running an older version of PBI (2022) at the moment (looking to upgrade shortly), but they want a report that filters to the current month/year and then also to the previous month/year so that when someone goes to the report, regardless of when, it will be set for the current month/year and then the previous month/year (example: December 2024 vs. December 2023).  

I can do that - but then here comes the part that I haven't cracked - they then also want the ability to slice to whatever date/month/year they want?  

So example, I visit the report and it's auto-set to show December 2024 vs. December 2023, but then the user wants to look at May 2024 vs. May 2023 or some other completely random dates.  So it needs to have the functionality to filter to the current month/year & previous year as well as be dynamic enough for a user to also then adjust it per their specifications.

 

Anyone have any ideas??? 

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi ianallen13V2 ,

     

    Maybe you can try formula like below:

    Current= 
VAR sel_year =
    SELECTEDVALUE ( 'Date'[Year] )
VAR sel_month =
    SELECTEDVALUE ( 'Date'[Month] )
RETURN
    IF (
        ISFILTERED ( 'Table' ),
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL('Table'),
                YEAR ( 'Table'[Date] ) = sel_year
                    && MONTH ( 'Table'[Month] ) = sel_month
            )
        ),        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL('Table'),
                YEAR ( [Date] ) = YEAR ( TODAY () )
                    && MONTH ( [Date] ) = MONTH ( TODAY () )
            )
        )
    )


    Previous= 
VAR sel_year =
    SELECTEDVALUE ( 'Date'[Year] )
VAR sel_month =
    SELECTEDVALUE ( 'Date'[Month] )
RETURN
    IF (
        ISFILTERED ( 'Table' ),
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL('Table'),
                YEAR ( 'Table'[Date] ) = sel_year
                    && MONTH ( 'Table'[Month] ) = sel_month
            )
        ),        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL('Table'),
                YEAR ( [Date] ) = YEAR ( TODAY () ) - 1
                    && MONTH ( [Date] ) = MONTH ( TODAY () )
            )
        )
    )

     

    Best Regards,
    Adamk Kong

     

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ianallen13V2 ,

     

    Maybe you can try formula like below:

    Current= 
VAR sel_year =
    SELECTEDVALUE ( 'Date'[Year] )
VAR sel_month =
    SELECTEDVALUE ( 'Date'[Month] )
RETURN
    IF (
        ISFILTERED ( 'Table' ),
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL('Table'),
                YEAR ( 'Table'[Date] ) = sel_year
                    && MONTH ( 'Table'[Month] ) = sel_month
            )
        ),        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL('Table'),
                YEAR ( [Date] ) = YEAR ( TODAY () )
                    && MONTH ( [Date] ) = MONTH ( TODAY () )
            )
        )
    )


    Previous= 
VAR sel_year =
    SELECTEDVALUE ( 'Date'[Year] )
VAR sel_month =
    SELECTEDVALUE ( 'Date'[Month] )
RETURN
    IF (
        ISFILTERED ( 'Table' ),
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL('Table'),
                YEAR ( 'Table'[Date] ) = sel_year
                    && MONTH ( 'Table'[Month] ) = sel_month
            )
        ),        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                ALL('Table'),
                YEAR ( [Date] ) = YEAR ( TODAY () ) - 1
                    && MONTH ( [Date] ) = MONTH ( TODAY () )
            )
        )
    )

     

    Best Regards,
    Adamk Kong

     

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

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Use a disconnected table to feed your slicer.  Use a measure to sense the slicer value and compute the desired date range (as 1 for included and 0 for excluded, for example) . Apply the measure as a visual filter (set to "equals 1", for example).

  • suparnababu8's avatar
    suparnababu8
    Super User
    1 year ago

    Hi ianallen13V2 

     

    Pls follow below steps

    1. create measures for Current and Previous Periods by using the below measure

      CurrentMonthNumber =
      CALCULATE(SUM('Table'[Value]),
      FILTER('DateTable',
      'DateTable'[Year] = YEAR(TODAY()) &&
      'DateTable'[Month] = MONTH(TODAY())))

      PreviousMonthNumber =
      CALCULATE(SUM('Table'[Value]),
      FILTER('DateTable',
      'DateTable'[Year] = YEAR(TODAY()) - 1 &&
      'DateTable'[Month] = MONTH(TODAY()))

    2. Create dynamic measures that respond to the slicer selections. as per below measures

      SelectedMonthValues =
      CALCULATE(SUM('Table'[Value]),
      FILTER('DateTable',
      'DateTable'[Year] = SELECTEDVALUE('DateTable'[Year]) &&
      'DateTable'[Month] = SELECTEDVALUE('DateTable'[Month])))

      SelectedPreviousMonthValues =
      CALCULATE(SUM('Table'[Value]),
      FILTER('DateTable',
      'DateTable'[Year] = SELECTEDVALUE('DateTable'[Year]) - 1 &&
      'DateTable'[Month] = SELECTEDVALUE('DateTable'[Month])))

    3. Then create a bookmark with the slicers set to the current month/year
    4. Set this bookmark as the default view when the report is opened.

     

    Let me know if it works

     

    Thanks!