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 is posssible to create a calculated column in calendar table that will have all the values present and I can use that column as slicer, if yes can you pls help with dax. or do I need to take another route ?

Thank you

Need Help

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

Multiple Date Range in Slicer.pbix56 KB

5 Replies

amitchandak
Super User
2 years ago
Seek08 , You can create measures for these. If needed you can also create calculation group for all these

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

calculation group authoring| Measure Slicer: https://youtu.be/VfxfJJ0RzvU

Using date table measures like

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
- PabloVallejo12
  Helper I
  2 years ago
  I think is more easy and efficient use bookmarks
  what do you think?
Seek08
Helper I
2 years ago
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 ?

Anonymous

Not applicable

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

Multiple Date Range in Slicer.pbix56 KB

Seek08
Helper I
2 years ago
Thank you, this helps.

Also there is one more article related to this:
https://www.sqlbi.com/articles/using-calculation-groups-or-many-to-many-relationships-for-time-intelligence-selection/

Forum Discussion

Multiple Date Range in Slicer

5 Replies

Related content

Custom Date Range Slicer

Limiting date range slicer

Date Range Slicer between Dynamic Dates

Multiple Numeric Range with Slicer

Text slicer: pasting multiple values