Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Excluding rows dynamically based on the slicer date range selection

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.

 

 

 

 

  • 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 ))

1 Reply

  • 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 ))