Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Changing date range on the chart based on slicer selection

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?

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      mmparvez
      Frequent Visitor
      2 years ago

      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