Dual Slicing

[ianallen13V2]

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??? 

[AndrewBell]

I'm also using an older version, I'm satisfied with it.

[Anonymous · Accepted solution]

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.

[suparnababu8]

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!

[lbendlin]

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).