Forum Discussion

untalbob's avatar
untalbob
Regular Visitor
5 years ago
Solved

Multiple column slicer that affects all visuals (date based)

Hi, 

 

I have seen similar problems and aproaches to the one I am having but not quite the solutions I am looking for which is having a single slicer based on multiple columns and that will affect all visuals on the page based on dates.

I have a calendar table, with all the days of the year, and based on that I have a few other columns that will indicate different "time statues" to call it someway; so I have a colum that indicates which dates correspond to the current month to date, year to date, current week ,current month, last week, last month, nest week, next month, etc. As seen on the image below. 

What I am trying to get is a single slicer with all the options on the different columns: 
○ Year to date
○ Month to date

○ Last Month

○ Current Month

○ Next Month

○ Last Week

○ Current Week

○ Next Week

The data on my report are sales reports so I have multiple visuals, and all reports have dates, so everything is affected by dates. 

Appreciate all your help. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi untalbob ,

     

    Here's a workaround. You can create a new table with a column dedicated to the slicer. And there is no relationship between the new table and the main table.

     

    Create a calculated column in the main table.

    Week = YEAR([Date])*1000+WEEKNUM([Date],2)

     

    Then you can create the measure as follows.

    Value1 =
SWITCH (
    SELECTEDVALUE ( Slicer[Category] ),
    "Year to date", TOTALYTD ( SUM ( 'Table'[Value] ), 'Table'[Date] ),
    "Month to date", TOTALMTD ( SUM ( 'Table'[Value] ), 'Table'[Date] ),
    "Last Month",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Table'[Date], TODAY (), -1, MONTH )
        ),
    "Current Month",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Table'[Date], TODAY (), 0, MONTH )
        ),
    "Next Month",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Table'[Date], TODAY (), 1, MONTH )
        ),
    "Last Week",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                [Week]
                    = YEAR ( TODAY () ) * 10000
                        + WEEKNUM ( TODAY (), 2 ) - 1
            )
        ),
    "Current Week",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', [Week] = YEAR ( TODAY () ) * 10000 + WEEKNUM ( TODAY (), 2 ) )
        ),
    "Next Week",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                [Week]
                    = YEAR ( TODAY () ) * 10000
                        + WEEKNUM ( TODAY (), 2 ) + 1
            )
        )
)

     

     

    Best Regards,

    Stephen Tao

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi untalbob ,

     

    Here's a workaround. You can create a new table with a column dedicated to the slicer. And there is no relationship between the new table and the main table.

     

    Create a calculated column in the main table.

    Week = YEAR([Date])*1000+WEEKNUM([Date],2)

     

    Then you can create the measure as follows.

    Value1 =
SWITCH (
    SELECTEDVALUE ( Slicer[Category] ),
    "Year to date", TOTALYTD ( SUM ( 'Table'[Value] ), 'Table'[Date] ),
    "Month to date", TOTALMTD ( SUM ( 'Table'[Value] ), 'Table'[Date] ),
    "Last Month",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Table'[Date], TODAY (), -1, MONTH )
        ),
    "Current Month",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Table'[Date], TODAY (), 0, MONTH )
        ),
    "Next Month",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Table'[Date], TODAY (), 1, MONTH )
        ),
    "Last Week",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                [Week]
                    = YEAR ( TODAY () ) * 10000
                        + WEEKNUM ( TODAY (), 2 ) - 1
            )
        ),
    "Current Week",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', [Week] = YEAR ( TODAY () ) * 10000 + WEEKNUM ( TODAY (), 2 ) )
        ),
    "Next Week",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                [Week]
                    = YEAR ( TODAY () ) * 10000
                        + WEEKNUM ( TODAY (), 2 ) + 1
            )
        )
)

     

     

    Best Regards,

    Stephen Tao

     

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