Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |