Forum Discussion
Multiple Date Range in Slicer
- Anonymous2 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
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
Thank you, this helps.
Also there is one more article related to this: