Exclude dimension values from table based on dynamic date selection

[ellenblackwell]

Hello Community,

I am trying to resolve a display issue that is driven by the time period selected by a user. Expression results based on the time period are working correctly. However, I am having trouble getting the proper dimension values to display based on the time period selected. How can I exclude Performer B/Organization 2 from the table below ?

Time Period Selected :	Full Prior Month :	3/1/19 - 3/31/19
	Performer	Organization	Org Activated Date	Org Deactivated Date	Transactions During Time Period Selected		Display Performer?
	A	1	3/15/2019	12/31/9999	0		Yes, because org active during a portion of the time period selected - regardless of 0 value in expression results
	B	2	1/1/2019	2/15/2019	0		I want to exclude this row, because org was deactivated before min date in time period selected. But if I suppress 0 values, I lose the row above, too.
	C	3	1/1/2019	3/15/2019	12		Yes, because org active during a portion of the time period selected
	D	4	1/1/2019	4/5/2019	17		Yes, because org active over the full time period selected

Many thanks in advance!

Ellen

[Vegar · Accepted solution]

You need a loose calendar/date table. In my example the calendar contains a field called Date and a field called Month (among  others).

Expression

sum({< [Org Activated Date] = {"<=$(=max(Date))"}, 
       [Org Deactivated Date] = {">=$(=min(Date))"}>}1)

You could of cource have another measure than SUM(1), but as long you keep this SET Modifier you will only get hits within the Org Activate-Deactivate period.

[ellenblackwell]

Thank you Vegar!