latest three months in slicer as default with filters of the prior dates in slicer

[niteshtrehan89]

Hi All,

I need a urgent help on one of my requirement on the Slicer. The requirement is we need to have a Slicer with lastest three month dates as default but it should also allow us to select the prior dates.

Suppose I have data from Feb 2020 till Jan 2021 then the default slicer should start from 1st Nov 2020 till 31st Jan 2021 but if I need the data to check from 1st March 2020 till Jan 2021 then the filter should allow us to select these dates. 

Please let me know if there is a work around on the same.

Thanks in advance.

[amitchandak · Accepted solution]

@niteshtrehan89 , You can check if the relative date slicer can fit in you need https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

Else you have to follow the approach to have a column that acts as a flag.

The first option I suggested will have dates other than three months .

[v-shex-msft · Accepted solution]

Hi @niteshtrehan89,

Unfortunately, current power bi does not support setting the default value of the slicer. You can take a look at the following methods if they suitable for your scenario.

If your field stores the regular date value, you can use this on slicer with 'relative' mode and choose 'last' 3 'month' then it will keep the specific range of records based on current date field values.

In addition, you can also create a calculated column to shows the 'tag' based on 'if statement' which compares with 'current date' and 'last date'. Then you can use this on a filter to choose matched tags to filter records.

If above methods not help, please share some dummy data to test.

How to Get Your Question Answered Quickly

Regards,
Xiaoxin Sheng

[niteshtrehan89]

@amitchandak 

the slicer is consisting the dates. I need to select the dates from the slicer.

[amitchandak · Accepted solution]

@niteshtrehan89 , You can check if the relative date slicer can fit in you need https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

Else you have to follow the approach to have a column that acts as a flag.

The first option I suggested will have dates other than three months .

[amitchandak]

@niteshtrehan89 ,You can create a column like

last three month =
Switch( True(),
[Date] >= eomonth(today(),-4)+1 && [Date] <= eomonth(today(),-1) ,"Last three " ,
[Date] & ""
)

or

last three month =
Switch( True(),
[Date] >= eomonth(today(),-4)+1 && [Date] <= eomonth(today(),-1) ,"Last three " ,
"Other dates"
)

and select Last three months from slicers