Return Campaigns that fall between the Start and End Date from Date Slicer

[jkenne01]

Hi,

I have been googling all day but have not managed to find a working solution for this one. If anyone can help would be greatly appreciated.

I have a Date Slicer set to between where I can pick a Start Date and an End Date.

I have a Table which contains a dataset of Campaigns which all have a Start Date and an End Date.

I would like to return any Campaigns whose Start Date and End Date fall between the Start Date and End date on the slicer.

For example if I have chosen 2018-08-01->2018-08-31 as the Date range for the slicer and I have a Campaign that runs from 2018-07-20->2018-08-2018 then this record should be returned.

I have looked at the below post that that seems to work based on a single date being chosen - not a range.

https://community.powerbi.com/t5/Desktop/Use-date-slicer-to-check-if-date-falls-between-two-dates/td...

Thanks for your help.

[TomMartens · Accepted solution]

Hey,

create a Calendar table (let's assume that this table is called daterange), that is not related to your campaigns table.

Create a measure in your campaigns table like so:

measurename = 
var startDaterange = MIN('daterange'[date])
var endDaterange = MAX('daterange'[date])
return
SUMX(
'campaign'
,
var startcampaign = 'campaign'[startdate]
var endcampaign = 'campaign'enddate]
return[
if(
AND(...
,1
,BLANK()
)

Create the proper condition that marks each campaign that falls into the selected daterange.

Now you can use this message in the "Visual levels filter" band to filter the campaigns.

Please consider to create a PBIX file that contains sample data, upload the pbix file to onedrive or dropbox and share the link.

Regards,

Tom

[v-jiascu-msft]

Hi @jkenne01,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Greg_Deckler · Accepted solution]

These two Quick Measures show how to do this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[TomMartens · Accepted solution]

Hey,

create a Calendar table (let's assume that this table is called daterange), that is not related to your campaigns table.

Create a measure in your campaigns table like so:

measurename = 
var startDaterange = MIN('daterange'[date])
var endDaterange = MAX('daterange'[date])
return
SUMX(
'campaign'
,
var startcampaign = 'campaign'[startdate]
var endcampaign = 'campaign'enddate]
return[
if(
AND(...
,1
,BLANK()
)

Create the proper condition that marks each campaign that falls into the selected daterange.

Now you can use this message in the "Visual levels filter" band to filter the campaigns.

Please consider to create a PBIX file that contains sample data, upload the pbix file to onedrive or dropbox and share the link.

Regards,

Tom