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

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!