Open OR Close date in one visualization

[Anonymous]

Hi all - 

I work at a Bank, we pull in our depositor data daily through a direct query. At any given time, we only have one 'production date' worth of data, which is a snapshot in time of our accounts. 

Our deposit data is a list of 300k+ accounts and the various attributes tied to these accounts (branch, region, product type, etc. etc.). Two of these attributes are Open Date, and Close Date. Below is an example of what the data would look like. 

Production Date	Account Nbr	Branch	Open Date	Close Date
9/23/2019	5614654863	1	9/2/2019
9/23/2019	6737585836	1	5/3/1982	7/1/2019
9/23/2019	8085103003	1	9/7/2019
9/23/2019	9702123603	1	1/15/1999	9/10/2019
9/23/2019	8316105946	1	3/30/2008
9/23/2019	7128090811	1	10/11/1992
9/23/2019	6109792123	2	7/11/1979
9/23/2019	5236964677	2	9/21/2019
9/23/2019	4488826866	2	8/14/2002	8/1/2019
9/23/2019	3847565885	2	12/16/1987
9/23/2019	3297913616	2	5/23/1975
9/23/2019	2826783099	2	8/14/1964

I'm trying to create a single table (or matrix) that shows the accounts opened and closed in the last 30 days by Branch. (The bolded items).

I can do them separately by setting a filter on Close Date or Open Date, respectively. But I am not sure how to put them together in one visual. I would need to show records where Close Date is in the last 30 days OR Open Date is in the last 30 days. 

Any help would be greatly appreciated!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures

o = DATEDIFF(MAX('Table'[open Date]),TODAY(),DAY)

c = DATEDIFF(MAX('Table'[Close Date]),TODAY(),DAY)

Measure 3 = IF([o]<>BLANK()&&[o]<=30,1,IF([c]<>BLANK()&&[c]<=30,1,0))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnmelbourne]

Or try

= CALCULATETABLE( VALUES(Accounts[ACCOUNT NBR]), OR( FILTER1, FILTER2) )

[amitchandak]

Join both Dates with the same date dimension.  One join will be enabled and another one will be disabled.

Now in calculate use "userelation" to activate the relation you want 

[johnmelbourne]

Try

= Calculate( sum (total deposits), OR( filter1, filter2 ) ) )


If you don't wrap your filters in an OR it treats them as an AND.