Forum Discussion
Open OR Close date in one visualization
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!
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
MaggieCommunity 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.
4 Replies
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
- Try
= Calculate( sum (total deposits), OR( filter1, filter2 ) ) )
If you don't wrap your filters in an OR it treats them as an AND. - Or try
= CALCULATETABLE( VALUES(Accounts[ACCOUNT NBR]), OR( FILTER1, FILTER2) ) 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
MaggieCommunity 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.
