Forum Discussion
Resolver ICustom Date Ranges based on Slicer Selection
Hi Experts,
I am trying to create custom date ranges that I will name and then drop into a slicer. Example is the time period 5/21/18 through 7/8/18 would be named C5-2018. 7/9/18 through 9/2/18 would be C6-2018 and so on.
I have created a table with the date ranges and names.
I have a second table that has sales by dates. I would like to load a slicer with the Capaign names in the previous table and the date ranges apply to the visualizations publishing the second tables data.
I am not sure where to begin with the measure. Any help / insight is appreciated!
Thanks
Dan
I would expand your campaign table to include all dates within each campaign range. You could then relate the tables by joining on the newly created date column in your campaign table to the date field in your Sales table.
Start with what you have in your campaign table, and add a custom column with the following statement:
List.Transform({Number.From(Date.From([Start Date]))..Number.From(Date.From([End Date]))}, each Date.From(_))
Then simply expand that list, and you will have every individual date reference a campaign number. You can then add a slicer to your dashboard with Campaign number, which will then filter your sales table because they are related.
So start with what you have:
Then add the custom column to give you this:
Then join the tables on the Date fields:
Then your output will be:
7 Replies
drewlewis15Solution Specialist
I would expand your campaign table to include all dates within each campaign range. You could then relate the tables by joining on the newly created date column in your campaign table to the date field in your Sales table.
Start with what you have in your campaign table, and add a custom column with the following statement:
List.Transform({Number.From(Date.From([Start Date]))..Number.From(Date.From([End Date]))}, each Date.From(_))
Then simply expand that list, and you will have every individual date reference a campaign number. You can then add a slicer to your dashboard with Campaign number, which will then filter your sales table because they are related.
So start with what you have:
Then add the custom column to give you this:
Then join the tables on the Date fields:
Then your output will be:
danbThanks for your help and it is working! The only issue i have is that my sales table is a monthly summary (dated on the first of the month). Is there any way that I can tweak the setup so that for C6-2018 i would be showing the sales including July instead of what is currently coming through is just August and September? In other words, can I expand the dates included to go back to the first of the month for the start date of the campaign window?
- drewlewis15
I can definitely help you tweak it to get that desired result, but I want to ask a question first as I am not familiar with your data. The campaigns have specific date ranges... How do you differentiate between campaigns when you have sales on 7/2/2018 (for example)? That date would fall within the C5-2018 campaign date range, but would fall within the C6-2018 campaign if you are using the start of the month column like your response indicates. I just want to make sure I help you include the sales into the correct campaign.
Hi danb,
You could try creating a new column in the Sales table using a nested IF condition:
Campaign = if(DATESBETWEEN('Sales'[Date], "05/21,2018", "07/08/2018"), "C5-2018",
(if(DATESBETWEEN('Sales'[Date], "07/09,2018", "09/02,2018"), "C6-2018",
(if(DATESBETWEEN('Sales'[Date], "09/03,2018", "10/07,2018"), "C7-2018",
(if(DATESBETWEEN('Sales'[Date], "10/08,2018", "11/11,2018"), "C8-2018",
(if(DATESBETWEEN('Sales'[Date], "11/12,2018", "12/30,2018"), "C9-2018", "Other")))))))))Then you could use this new column as a slicer.
Hope this works.
- danb
Hi Anonymous,
This does work however I over time I would have to keep editing the equation and it would become significantly complicated as new windows are layered in.
