Required custom date Slicer Last 7 days,last 15 days,last 30 days

[Anonymous]

Hi Team,

I have 3 columns 
ID LastUpdateddate Values
1   2020-08-10          100
2   2020-07-12           200
3   2020-08-01            500
4   2020-08-05           700
5    2020-05-15          800
6    2020-07-30          100

I need a date slicer in that i required last7days,last15days,last30 option

once i select last7 days filter , table should display last 7days records only and so on for other option.

Date filter
last7days
last15days
last30days

[Anonymous]

Hi @v-zhenbw-msft ,

thanks for the amazing answer! What if I want to make this more dynamic, so that I don't have to do it for every KPI but use it with selected measure? 

[Anonymous]

Thanks for the help

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

If you want to create slicer like following screenshot, we can use the following ways to meet your requirement.

1. Create a date table and a date slicer table. There is no relationship among tables.

Date = CALENDAR("2020/5/1","2020/8/31")

2. Create three measure to calculate the last 7 days, last 15 days and last 30 days.

Last7days = 
var _select = SELECTEDVALUE('Date'[Date])
var _last7days = _select-7
var _last7values = CALCULATE(SUM('Table'[Values]),FILTER('Table','Table'[LastUpdateddate]<=_select&&'Table'[LastUpdateddate]>=_last7days))
return
IF(
    ISFILTERED('Date'[Date]),
    _last7values,
    SUM('Table'[Values]))

Last15Days = 
var _select = MIN('Date'[Date])
var _last15days = _select-15
var _last15values = CALCULATE(SUM('Table'[Values]),FILTER('Table','Table'[LastUpdateddate]<=_select&&'Table'[LastUpdateddate]>=_last15days))
return
IF(
    ISFILTERED('Date'[Date]),
    _last15values,
    SUM('Table'[Values]))

Last30Days = 
var _select = MIN('Date'[Date])
var _last30days = _select-30
var _last30values = CALCULATE(SUM('Table'[Values]),FILTER('Table','Table'[LastUpdateddate]<=_select&&'Table'[LastUpdateddate]>=_last30days))
return
IF(
    ISFILTERED('Date'[Date]),
    _last30values,
    SUM('Table'[Values]))

3. At last we can create a measure to get the result.

Result measure = 
var _select = SELECTEDVALUE('Date filter'[slicer])
return
IF(
    ISFILTERED('Date filter'[slicer]),
    SWITCH(
        TRUE(),
        _select="last7days",[Last7days],
        _select="last15days",[Last15Days],
        _select="last30days",[Last30Days]
    ))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[ooooofriend]

I've been looking for a similar solution and this is the closest to what i'm after i think. so i'm hoping you can help. I'm very new so this may be stupid question. but i have 2 date fields for my data, sometimes i want to filter my table by 'creation date', and sometimes i want to use 'Dispatch Date'. i only want to have 1 slicer and just a button or something to swap between which data the slicer is using. seems kind of similar to what you're doing here but i cant work out what i'd need to change to make it work.

Thanks for any help you may be able to give

[Anonymous]

This worked perfectly for a report I am working on.

Only needed a few changes as the start date for me didn't need selection so no Date table. Also used EDATE to work with months instead of days. Brilliant solution 🙂

Last3months = 

var _today = TODAY()
var _last3months = CALCULATE('Table'[Value],
DATESBETWEEN('Table'[MostRecentActivity], EDATE(_today, -3), _today))
return _last3months

[Kiwizqt]

Amazingly detailed answer

[amitchandak]

@Anonymous , I think the same issue as -

https://community.powerbi.com/t5/Desktop/required-custom-date-sllicer-Last-7-days-last-15-days-last-30/m-p/1284551#M561530

Else create slicer like this -

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

And use rolling measure -

Rolling 7 days = CALCULATE(sum(Sal[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-7,Day))

Rolling 14 days = CALCULATE(sum(Sal[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-14,Day))

In case you need to display all dates

https://www.youtube.com/watch?v=duMSovyosXE

[parry2k]

@Anonymous   use relative date filtering

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k But user wants custom date filter, they dont need relative filter,

[parry2k]

@Anonymous check this post and tweak it as per your need.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡