Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
79 | |
67 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |