Forum Discussion
Custom Date Ranges based on Slicer Selection
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:
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:
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?
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.
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
