Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dynamic date range filter between tables with no relation

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_dtproductsale_amtPandL
1/1/2020Product A110Profit
1/2/2020Product B90Loss
1/3/2020Product C200Profit
1/4/2020Product D90Loss
1/5/2020Product A90Loss
1/6/2020Product B150Profit
1/7/2020Product C200Profit
1/8/2020Product D100Profit
1/9/2020Product A120Profit
1/10/2020Product B100Profit
1/11/2020Product C90Loss
1/12/2020Product D115Profit
1/13/2020Product A90Loss
1/14/2020Product B80Loss
1/15/2020Product C70Loss
1/16/2020Product D65Loss
1/17/2020Product A77Loss
1/18/2020Product B78Loss
1/19/2020Product C67Loss

 Table 2:

Ad campaignad_st_dtad_ed_dt
Ad 11/1/20201/5/2020
Ad 21/6/20201/12/2020
Ad 31/13/20201/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..!

Need Help
Show and Tell
Tips and Tricks

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        Hi,

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

        Hope this helps.