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
Anonymous
Not applicable

Sync chiclet slicer to a date filter

Hello everyone, I have a specific requirement in my report to sync a quick select date range(1W,1M,3M,6M,9M,1Y) which I plotted using a chiclet slicer to a date filter which I plotted using the slicer option such that if I select 1week or 1month option in the quick select, the date filter from and to should change from today to the past 1week date or 1month date. 

I tried using the sync slicer option and under advanced options I tried to group them with same name but its not working as per my requirement. Can anyone please help me on this.  

lavanyaanupindi_0-1673241007192.png

lavanyaanupindi_0-1673241436651.png

 

   @amitchandak @AllisonKennedy @MFelix 

2 REPLIES 2
AllisonKennedy
Super User
Super User

@Anonymous apologies for my delayed reply.

 

You don't need sync slicer here, just need the data model to be set up correctly. 

 

Where are you getting the quick select date range(1W,1M,3M,6M,9M,1Y) info from? This needs to be either:

Option A) in the DimDate table itself,

Option B) related to the DimDate table, or

Option C) you need to create a DAX measure that you can then use to filter the date slicer

 

I don't see a way to do Option B without Option A (and it would require a many to many relationship),

 

My Recommendation:

Add a new blank query and paste the below code:

 

let
// Relative Date Slicer table courtesy of www.excelwithallison.com
Credit = "www.ExcelwithAllison.com",
// Edit this start date to match your dataset.
startDate = Date.From(DateTime.LocalNow()),
#"Converted to Table" = #table({"Today"}, {{startDate}}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Today", type date}}),
#"Inserted Start of Year" = Table.AddColumn(#"Changed Type", "Start of Year", each Date.StartOfYear([Today]), type date),
#"Inserted Start of Month" = Table.AddColumn(#"Inserted Start of Year", "Start of Month", each Date.StartOfMonth([Today]), type date),
#"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Start of Month", "Start of Quarter", each Date.StartOfQuarter([Today]), type date),
#"Inserted Start of Week" = Table.AddColumn(#"Inserted Start of Quarter", "Start of Week", each Date.StartOfWeek([Today]), type date),
#"Inserted 1 Week Ago" = Table.AddColumn(#"Inserted Start of Week", "1 Week Ago", each Date.AddDays([Today],-7), type date),
#"Inserted 1 Month Ago" = Table.AddColumn(#"Inserted 1 Week Ago", "1 Month Ago", each Date.AddMonths([Today],-1), type date),
#"Inserted 3 Months Ago" = Table.AddColumn(#"Inserted 1 Month Ago", "3 Months Ago", each Date.AddMonths([Today],-3), type date),
#"Inserted 6 Months Ago" = Table.AddColumn(#"Inserted 3 Months Ago", "6 Months Ago", each Date.AddMonths([Today],-6), type date),
#"Inserted 9 Months Ago" = Table.AddColumn(#"Inserted 6 Months Ago", "9 Months Ago", each Date.AddMonths([Today],-9), type date),
#"Inserted 1 Year Ago" = Table.AddColumn(#"Inserted 9 Months Ago", "1 Year Ago", each Date.AddMonths([Today],-12), type date),
#"Demoted Headers" = Table.DemoteHeaders(#"Inserted 1 Year Ago"),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Renamed Columns" = Table.RenameColumns(#"Transposed Table",{{"Column1", "Date Range"}, {"Column2", "Start Date"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Date", type date}, {"Date Range", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Sort", 0, 1, Int64.Type)
in
#"Added Index"

 

 

Tweak it as you see fit, but use this as your date range slicer values. 

Then, follw the method I used in this blog I wrote here: https://excelwithallison.blogspot.com/2021/09/power-bi-forecasting-with-irregular.html to get this to filter your date slicer and data. 🙂 

 

Let me know how you get on! 🙂 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

amitchandak
Super User
Super User

@Anonymous , Sync slicer only works with the only same type of slicer, will not sync even list and range

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.