Multiple column slicer that affects all visuals (date based)

[untalbob]

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. 

[Anonymous · Accepted solution]

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.

[TomMartens]

Hey @untalbob ,

what you are looking for can be achieved by following the idea of creating a single period column, this idea has been mentioned by @GilbertQ  in this blog Create Dynamic Periods for Fiscal or Calendar Dates in Power BI - Reporting/Analytics Made easy with...

Hopefully, this will provide ideas to tackle your challenge.

Regards,

Tom