Different Date Slicers Controlling Different Table Visuals in the same page

[superhayan]

Hello all,

I have spent hours on this problem which is seemingly simple but I am still stuck. So hopefully someone can give me a helping hand.

I have 2 data tables: one is "Deal" table of deal information with columns of Deal ID, Close Date, Promo Date , Stage, Revenue, etc. Another table is a calendar table taking min and max of Close Date from "Deal Table" as the earliest and latest date. The 'Calendar[Date]' has an active 1:many relationship with 'Deal[Close Date]'.

In visualization view, I have a slicer of 'Calendar[Date]'. I also have a table visual with deal info. Therefore when I select the whole Sep2024 in the slicer, the table will show all deal records that are closing in Sep2024.

Now I want to add a second table visual to the same page looking at deals with Promo Dates that are in certain period back from the chosen Close Date. Ideally I want to have another slicer that I can choose from 1-12 months from the date chosen in Close Date slicer but in the Promo Date perspective. For example, if I choose Sep2024 in Close Date slicer and 3 months in the new slicer, the the new table visual will show all deals with Promo Dates during 1/7/2024-30/9/2024, regardless of the close date. If I choose Aug2024 in Close Date slicer and 1 month in the new slicer, then the table will show all deals with Promo Date during 1-30 Aug2024 regardless of the Close Date. I don't want the second table to be limited by the Close Date slicer.

Now I am able to make the new slicer and get second table to show deals with Promo Dates in a specific period from the chosen Close Date but ONLY those closing in the chosen days in the Close Date slicer. I want to see deals with ALL close dates within the chosen Promo Date period instead. I tried to disconnect interaction between second table visual and Close Date slicer but it still doesnt work because the measures I built to get deals with chosen Promo Date period is based on Max('Calendar[Date]').

Sorry that I cannot share any data as my laptop is company protected. Please let me know if any clarification is needed.

Thank you!!!

[amitchandak · Accepted solution]

@superhayan , if you want to apply the same month filter both on the close date and the Promo date. Join it again with a calendar, join will be inactive, you can activate using userelationship

calculate( Countrows(Table),USERELATIONSHIP ('Table'[promocode], 'Calendar'[Date]))

In case you need the dates which are based on close date, date selected because the close date was filtered by calendar

Measure =

Var _min = minx(allselected(Table), Table[Promo Date]) // Min Based on allselection

Var _max = Minx(allselected(Table), Table[Promo Date]) // Min Based on allselection

return

calculate(Countrows(Table),filter(all('Calendar'), 'Calendar'[Date] >= _Min && 'Calendar'[Date] <= _max))

[v-zhouwen-msft · Accepted solution]

Hi amitchandak ,thanks for the quick reply, I'll add more.

Hi @superhayan ，

The Table data is shown below:

If I understand correctly, in this case your expected result is the following.(4/1/2024 - 5/31/2024)

For the second table visual, I created an additional table with 'Deal ID', which will not be filtered because it has no relationship with the table 'Deal'.

Then create a measure using the following expression.

Close_Date = 
VAR _secondslicer = SELECTEDVALUE('Table'[Value])
VAR _begindate = EOMONTH(MINX(DATEADD('Calendar'[Date],-_secondslicer + 1,MONTH),[Date]),-1) + 1
VAR _enddate = EOMONTH(MAX('Calendar'[Date]),0)
VAR _table = FILTER(ALL(Deal), [Promo Date] >= _begindate && [Promo Date] <= _enddate && [Deal ID] = SELECTEDVALUE('Table 2'[Deal ID]))
RETURN MAXX(_table,[Close Date])

 Final output

If I understand wrongly, please correct me.

Best Regards,
Wenbin Zhou

[amitchandak · Accepted solution]

@superhayan , if you want to apply the same month filter both on the close date and the Promo date. Join it again with a calendar, join will be inactive, you can activate using userelationship

calculate( Countrows(Table),USERELATIONSHIP ('Table'[promocode], 'Calendar'[Date]))

In case you need the dates which are based on close date, date selected because the close date was filtered by calendar

Measure =

Var _min = minx(allselected(Table), Table[Promo Date]) // Min Based on allselection

Var _max = Minx(allselected(Table), Table[Promo Date]) // Min Based on allselection

return

calculate(Countrows(Table),filter(all('Calendar'), 'Calendar'[Date] >= _Min && 'Calendar'[Date] <= _max))