Excluding rows dynamically based on the slicer date range selection

[Anonymous]

Hello All,

I have a table named Revenue by Account with the below columns.

CampaignName	LeadID	LeadCreatedDate	CampaignStartDate	Revenue
Referral	20	01-Ap-2021	01-Mar-21	$50
Direct	25	01-May-21	01-Jan-21	$200
others	32	01-Jun-21	01-May-21	$250
Referral	80	01-Sep-21	01-Mar-21	$100
Direct	90	01-Nov-21	01-Jan-21	$20
others	95	01-Dec-21	01-May-21	$6

I have created a date table and created relationship with the date table and LeadCreatedDate of the above table.

Now I am creating a matrix visual to Show total revenue by each campaign with the Date slicer from the date table.

So when a user select a date range, it will show the campaignname and revenue for the leads created with the time range.

For Example, if user selects date from May-21 to Sep-21, it will show the Campaigns and revenue where the leads created within May-21 to Sep-21. In this case, it will show the rows 2,3,4 from above table.

Now, I have a requirement that, if any Campaign start date falls between the selected date range, then it should not show on the report.

if user selects date from May-21 to Sep-21, it should show only rows 2 & 4, because row 3 has a campaign with start date in May-21.

Please help me in achieving this.

[amitchandak · Accepted solution]

@Anonymous , Slicer needs to on independent date table. And you need a measure like

//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[LeadCreatedDate] >=_min && 'Table'[LeadCreatedDate] <=_max
&& 'Table'[CampaignStartDate] <_min && 'Table'[CampaignStartDate] >_max ))