Sync chiclet slicer to a date filter

[Anonymous]

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.  

   @amitchandak @AllisonKennedy @MFelix 

[AllisonKennedy]

@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! 🙂 

[amitchandak]

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