Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 @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
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
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |