Changing date range on the chart based on slicer selection

[Anonymous]

Hi,

I would like to be able to change the date range on a chart based on a slicer.

This would be similar to relative time slicer but I want to be able to define the time periods in the table and then use them in a slicer:

So if I select "Last 3 Days" my chart shows data for 3 days only, if I select "Last Month" my chart shows data for last month...

What is the best way to achieve that?

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

Create an extra table to list all avialble date periods for selection, which is unrelated to source data table.

Add below measure into visual level filter.

Measure =
VAR selectedperiod =
    SELECTEDVALUE ( 'Date range'[Period] )
VAR last3days =
    IF (
        MAX ( Test_1[Date] ) <= TODAY ()
            && MAX ( Test_1[Date] )
                > TODAY () - 3,
        1,
        0
    )
VAR last6days =
    IF (
        MAX ( Test_1[Date] ) <= TODAY ()
            && MAX ( Test_1[Date] )
                > TODAY () - 6,
        1,
        0
    )
VAR lastweek =
    IF (
        YEAR ( MAX ( Test_1[Date] ) ) = YEAR ( TODAY () )
            && WEEKNUM ( MAX ( Test_1[Date] ), 2 )
                = WEEKNUM ( TODAY (), 2 ) - 1,
        1,
        0
    )
VAR last12days =
    IF (
        MAX ( Test_1[Date] ) <= TODAY ()
            && MAX ( Test_1[Date] )
                > TODAY () - 12,
        1,
        0
    )
VAR last15days =
    IF (
        MAX ( Test_1[Date] ) <= TODAY ()
            && MAX ( Test_1[Date] )
                > TODAY () - 15,
        1,
        0
    )
VAR lastmonth =
    IF (
        MONTH ( TODAY () ) <> 1,
        IF (
            YEAR ( MAX ( Test_1[Date] ) ) = YEAR ( TODAY () )
                && MONTH ( MAX ( Test_1[Date] ) )
                    = MONTH ( TODAY () ) - 1,
            1,
            0
        ),
        IF (
            YEAR ( MAX ( Test_1[Date] ) )
                = YEAR ( TODAY () ) - 1
                && MONTH ( MAX ( Test_1[Date] ) ) = 12,
            1,
            0
        )
    )
RETURN
    IF (
        selectedperiod = "Last 3 days",
        last3days,
        IF (
            selectedperiod = "Last 6 days",
            last6days,
            IF (
                selectedperiod = "Last Week",
                lastweek,
                IF (
                    selectedperiod = "Last 12 days",
                    last12days,
                    IF (
                        selectedperiod = "Last 15 days",
                        last15days,
                        IF ( selectedperiod = "Last Month", lastmonth, 0 )
                    )
                )
            )
        )
    )

Best regards,

Yuliana Gu

[mmparvez]

Hi Yuliana,

Can you please explain "Add below measure into visual level filter.".

Can you please review with the current version of Power BI desktop.

Regards'

Parvez