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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors