Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
karimkz
Advocate I
Advocate I

How they did that with chiclet slicer?

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? 

chiclet

 

 

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I've started writing but I see @magsod 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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

I've started writing but I see @magsod 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

magsod
Solution Supplier
Solution Supplier

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.