Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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))
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))
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