Forum Discussion
Seek08
2 years agoHelper I
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 ...
- 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
Seek08
2 years agoHelper I
Thanks a lot for your response.
I need a slicer with these values, so that what ever is selected in this slicers all calculations in the reports happens on that selection. Can we create a column and that can be used in slicer ?