Forum Discussion

Seek08's avatar
Seek08
Helper I
2 years ago
Solved

Multiple Date Range in Slicer

Hi All,   I need to provide multiple date ranges filter on power bi reports such as: Current Week, Last week, MTD, YTD, Rolling 3 months, Last 3 monts etc. I have a Calendar table in my model. Is ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from amitchandak  and PabloVallejo12 , please allow me to provide another insight: 

     

    Hi  Seek08 ,

    Here are the steps you can follow:

    1. Enter data – Slicer table.

    2. Create measure.

    Flag =
    VAR _today =
        TODAY ()
    VAR _Rolling3Monthsmindate =
        EOMONTH ( _today, -4 )
    RETURN
        SWITCH (
            TRUE (),
            MAX ( 'Slicer_Table'[Slicer] ) = "Current Month"
                && YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( _today )
                && MONTH ( MAX ( 'Table'[Date] ) ) = MONTH ( _today ), 1,
            MAX ( 'Slicer_Table'[Slicer] ) = "Last Month"
                && YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( _today )
                && MONTH ( MAX ( 'Table'[Date] ) )
                    = MONTH ( _today ) - 1, 1,
            MAX ( 'Slicer_Table'[Slicer] ) = "Rolling 3 Months"
                && MAX ( 'Table'[Date] ) > _Rolling3Monthsmindate
                && MAX ( 'Table'[Date] ) <= EOMONTH ( _today, 0 ), 1,
            MAX ( 'Slicer_Table'[Slicer] ) = "Current Week"
                && YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( _today )
                && WEEKNUM ( MAX ( 'Table'[Date] ), 2 ) = WEEKNUM ( _today, 2 ), 1,
            MAX ( 'Slicer_Table'[Slicer] ) = "Last Week"
                && YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( _today )
                && WEEKNUM ( MAX ( 'Table'[Date] ), 2 )
                    = WEEKNUM ( _today, 2 ) - 1, 1,
            MAX ( 'Slicer_Table'[Slicer] ) = "Yesterday"
                && MAX ( 'Table'[Date] ) = _today - 1, 1,
            MAX ( 'Slicer_Table'[Slicer] ) = "Last 3 Month"
                && MAX ( 'Table'[Date] ) > EOMONTH ( _today, -5 )
                && MAX ( 'Table'[Date] ) <= EOMONTH ( _today, -1 ), 1,
            MAX ( 'Slicer_Table'[Slicer] ) = "MTD"
                && MAX ( 'Table'[Date] ) > EOMONTH ( _today, -1 )
                && MAX ( 'Table'[Date] ) <= _today, 1,
            MAX ( 'Slicer_Table'[Slicer] ) = "YTD"
                && MAX ( 'Table'[Date] ) >= DATE ( YEAR ( _today ), 1, 1 )
                && MAX ( 'Table'[Date] ) <= _today, 1
        )
    
    Sum_Measure =
    SUMX(ALLSELECTED('Table'),'Table'[rand])

    3. Place [Flag]in Filters, set is=1, apply filter.

    4. Result:

     

    Best Regards,

    Liu Yang

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