Forum Discussion
Slicer Overlapping Dates
I am trying to create a slicer with the selection options of "Last 12 Months", "Current Year", "Previous Year", and "Previous 2 Years". I have been able to link these slicers properly, but I have to use two slicers: one for last 12 months and one for the other 3 options. The data comes from years 2019-2022 typically, and this causes Last 12 Months and Previous Year to sometimes overlap (hence why I need two different slicers) causing some of the data to only show under one field, not both. Is there a way to go around this and mesh all four options into one slicer while allowing the same values to be used in two groupings?
Anonymous , You can all these 4 values in an independent table. and change value based on that
If you want to select a date and then want 12 months trend (less dates and more values on axis) , you need and independent date table too
example of last 12 months
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))example video to work on slicer
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
You can use date table if there is no filter on the range is bigger the range you require
If you simply need rolling then refer
https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79
Hi Anonymous ,
I created some data:
Here are the steps you can follow：
1. Use Enter data to create a table as a slicer.
2. Create measure.
Current Year = var _today=TODAY() return IF( MAX('Table'[Date])>=DATE(YEAR(_today),1,1)&& MAX('Table'[Date])<=TODAY(),1,0)Last 12 Months = var _today=TODAY() return IF( MAX('Table'[Date])>=DATE(YEAR(_today),MONTH(_today)-12,DAY(_today))&& MAX('Table'[Date])<=TODAY(),1,0)Previous 2 Year = var _today=TODAY() return IF( MAX('Table'[Date])>=DATE(YEAR(_today)-2,1,1)&& MAX('Table'[Date])<=EOMONTH(_today,-12),1,0)Previous Year = var _today=TODAY() return IF( MAX('Table'[Date])>=DATE(YEAR(_today)-1,1,1)&& MAX('Table'[Date])<=EOMONTH(_today,-12),1,0)Flag = SWITCH( TRUE(), MAX('Slicer table'[Slicer])="Last 12 Months",[Last 12 Months], MAX('Slicer table'[Slicer])="Current Year",[Current Year], MAX('Slicer table'[Slicer])="Previous Year",[Previous Year], MAX('Slicer table'[Slicer])="Previous 2 Years",[Previous 2 Year])3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Anonymous , You can all these 4 values in an independent table. and change value based on that
If you want to select a date and then want 12 months trend (less dates and more values on axis) , you need and independent date table too
example of last 12 months
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))example video to work on slicer
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
You can use date table if there is no filter on the range is bigger the range you require
If you simply need rolling then refer
https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79
Hi Anonymous ,
I created some data:
Here are the steps you can follow：
1. Use Enter data to create a table as a slicer.
2. Create measure.
Current Year = var _today=TODAY() return IF( MAX('Table'[Date])>=DATE(YEAR(_today),1,1)&& MAX('Table'[Date])<=TODAY(),1,0)Last 12 Months = var _today=TODAY() return IF( MAX('Table'[Date])>=DATE(YEAR(_today),MONTH(_today)-12,DAY(_today))&& MAX('Table'[Date])<=TODAY(),1,0)Previous 2 Year = var _today=TODAY() return IF( MAX('Table'[Date])>=DATE(YEAR(_today)-2,1,1)&& MAX('Table'[Date])<=EOMONTH(_today,-12),1,0)Previous Year = var _today=TODAY() return IF( MAX('Table'[Date])>=DATE(YEAR(_today)-1,1,1)&& MAX('Table'[Date])<=EOMONTH(_today,-12),1,0)Flag = SWITCH( TRUE(), MAX('Slicer table'[Slicer])="Last 12 Months",[Last 12 Months], MAX('Slicer table'[Slicer])="Current Year",[Current Year], MAX('Slicer table'[Slicer])="Previous Year",[Previous Year], MAX('Slicer table'[Slicer])="Previous 2 Years",[Previous 2 Year])3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly