Multiple Date Range in Slicer

[Seek08]

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

[Anonymous · Accepted solution]

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]

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-intel...

[Seek08]

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 ?

[amitchandak]

@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]

I think is more easy and efficient use bookmarks
what do you think?