Custom Date Ranges based on Slicer Selection

[danb]

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

[drewlewis15 · Accepted solution]

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:

[danb]

@drewlewis15

Thanks 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.     

[danb]

@drewlewis15

I understand it is a bit of a weird request. However what I am trying to do is show by campaign window the spend associated with those months. I am not trying to break out the sales to a per day basis. More I just want to see for C5-2018 i want to show May, June and July, and for C6-2018 I would show July, August and September and so on. Hopefully this makes sense. 

Thanks

Dan

[danb]

@drewlewis15

I think i figured it out. I ended up creating two running calendars. The first is simply a list of the days of the year

I then modified the campaign calendar to overlap the start dates back to the first of the month. 

I then just tied the relationships out and everything is working as expected. Thanks again for your help! 

Dan

[Anonymous]

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.