How they did that with chiclet slicer?

[karimkz]

Hi all. 

Just got a screenshot of a webinar about Power BI from my colleague (Russian language).

The chiclet slicer bars are: Today, Yesterday, This week, Last month, This month, This quarter, 3 months, 6 monts etc. 

Can't get how they did such kind of a filter/slicer. Any ideas? 

Thanks!

[Anonymous · Accepted solution]

I've started writing but I see @Anonymous has given a solution.

Nevertheless - maybe some example will help.

I would try creating a disconnected dimension - a table named SmartDatesSlicer with 3 columns:

SlicerValue | FromDate   | ToDate   

Values for example (dates in DD/MM/YYYY format):

SlicerValue  | FromDate    | ToDate   

Yesterday    | 24/04/2017 | 24/04/2017   

PrevMonth  | 01/03/2017 | 31/03/2017

WeekBefore| 16/04/2017 | 22/04/2017

etc...

Of course, the dates have to be formulas calculated relative to the current date.

Then, you create a measure such as:

SalesAmt = Calculate(Sum(Fact[Sales]),DATESBETWEEN(CalendarTable{Date],Max(SmartDatesSlicer[FromDate]),Max(SmartDatesSlicer[ToDate])))

Where CalenaderTable is the regular Dates table connected to Fact.

Good luck

Michael

[Anonymous]

Since your goal would be to restrict the measures in the visual to only show a certain period in time I would suggest to use a filter with datesbetween() in the measures. The Start and End date in the filter is then calculated appropriately in the context of the filtered selection in the separate slicer table (today, this week last month etc.) Maybe even include the calculated start and end dates in the slicer table?

This is atleast one way to make it work... 

Br,

Magnus

[opticshrew]

Hi there,

I'm in agreement with magsod. I would create a custom column which would then break down set time frames and associate a name/tag.

These can then be applied to the chiclet slicer which would then provided the desired outcome.

Thanks,

J