Dynamic date range filter between tables with no relation

[Anonymous]

Hi All,

I have scenario where i have 2 tables with no relationship between them  but wanted to enforce a slicer with date range effefct ex:

Table 1

Trans_dt	product	sale_amt	PandL
1/1/2020	Product A	110	Profit
1/2/2020	Product B	90	Loss
1/3/2020	Product C	200	Profit
1/4/2020	Product D	90	Loss
1/5/2020	Product A	90	Loss
1/6/2020	Product B	150	Profit
1/7/2020	Product C	200	Profit
1/8/2020	Product D	100	Profit
1/9/2020	Product A	120	Profit
1/10/2020	Product B	100	Profit
1/11/2020	Product C	90	Loss
1/12/2020	Product D	115	Profit
1/13/2020	Product A	90	Loss
1/14/2020	Product B	80	Loss
1/15/2020	Product C	70	Loss
1/16/2020	Product D	65	Loss
1/17/2020	Product A	77	Loss
1/18/2020	Product B	78	Loss
1/19/2020	Product C	67	Loss

 Table 2:

Ad campaign	ad_st_dt	ad_ed_dt
Ad 1	1/1/2020	1/5/2020
Ad 2	1/6/2020	1/12/2020
Ad 3	1/13/2020	1/19/2020

 i wanted to create a slicer/filter for "Ad campaign", if a value is selected, i wanted to filter Table1."Trans_dt" between Table2."ad_st_dt" and Table2."ad_ed_dt" .

Thank you for the help..!

[Ashish_Mathur]

Hi,

Write this calculated column formula in Table1

=CALCULATE(max(Table2[Ad campaign]),FILTER(Table2,Table2[ad_st_dt]<=EARLIER(Table1[Trans_dt])&&Table2[ad_ed_dt]>=EARLIER(Table1[Trans_dt])))

Hope this helps.

[Anonymous]

Hello Mathur,

Thank you for the help, the solution is partially working, the is issue if an Ad campaign exists with over lapping date range with another campaign.

please consider this example:

I introduced "Ad 4" with date ranging from "07/01/2020' till '10/01/2020' which is with in the range of "Ad 2". I wanted to see 'Ad 2' for all dates from '06/01/2020' till '12/01/2020' , but the dates 07/01/2020' till ' 10/01/2020' are allocated to 'Ad 4' causing these rows to drop.

After applying the filter:

if we duplicate the dates then i think the sales amount will be duplicated causing wrong total ? 

Thanks

Ani

[Ashish_Mathur]

Hi,

In my formula, replace the MAX() with MIN().

Hope this helps.

[Anonymous]

Hi Ashish,

if i do MIN() then "ad 4" is not showing up in the slicer or  in the data, can't we apply the range on Table1 based on selected values of Table 2 ? 

I appriciate your help..!

Thanks

Ani

[Ashish_Mathur]

Hi,

Ad4 will obviously not show up in the Table because the dates in that ad are subsumed in Ad2.

[Anonymous]

Hi Ashish,

I understand and this was my challenge, i have to show the "Ad 4" as well and when selected need to filter on dates between 7/01/2020 till 10/01/2020, the sales and total should be intact when no "Ad" is selected. Do you think its possible ?

Thanks

Ani

[amitchandak]

@Anonymous , not very clear

Try a measure like

measure =
var _max = maxx(allselected(Date),Table[Date])
var _min = minx(allselected(Date),Table[Date])

return
calculate(sum(Table[value]),filter(Table, Table[Date] >=_min && Table[Date] <=_max))

Also, refer

https://www.youtube.com/watch?v=duMSovyosXE

[Anonymous]

Hi Amit,

I tried the solution you mentioned, created a table 

Table =

VAR SDATE = SELECTEDVALUE(Table2[ad_st_dt])

VAR EDATE = SELECTEDVALUE(Table2[ad_ed_dt])

RETURN

FILTER(Table1, Table1[Trans_dt] >= SDATE && Table1[Trans_dt] <= EDATE)

I don't see any data when i tried to select attributes from new derived "Table" (highlighted in red square)

Thanks

Anikah

[Anonymous]

Thank for your super quick advice, i updated my scenario.