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.
I have two tables
Table 1
Admins Table | |
Processingadmin_ID | Admin_Name |
1 | Admin1 |
2 | Admin2 |
3 | Admin3 |
Table 2: Loan Table with Created date of last two months: April and May
Loans Table | ||||
LeadID | Lead_Created_Date | Funding_Amount | Approve_Date | Processingadmin_ID |
32 | 01/05/21 | 5000 | 05/05/21 | 1 |
33 | 02/05/21 | 6000 | 06/05/21 | 2 |
34 | 03/04/21 | 0 | 03/04/21 | 3 |
35 | 04/04/21 | 7000 | 04/05/21 | 1 |
36 | 05/05/21 | 5000 | 05/05/21 | 2 |
37 | 06/05/21 | 2000 | 08/05/21 | 3 |
38 | 07/05/21 | 3 |
I want output in following format
Required Output | |||
Admin_Name | Approved Loans Count | Leads Count | New Loan Count |
Admin1 | 2 | 1 | 0 |
Admin2 | 2 | 2 | 1 |
Admin3 | 0 | 1 | 0 |
Logics to be used:
From | To | |||
Selected Date (Slicer) | Using | Lead_Created_Date | 01/05/21 | 07/05/21 |
Approved Loans Count Logic | Count of LeadID from Loans Table if Funding_Amount>0 and Approve_Date is not blank and Approve_Date is between date selected in slicer |
Leads Count Logic | Count of LeadID from Loans Table if Lead_Created_Date is between date selected in slicer |
New Loan Count | Count of LeadID from Loans Table if Funding_Amount>0 and Approve_Date is not blank and Approve_Date is between date selected in slicer and Lead_Created_Date=Approve_Date |
Approved Loan Count should be basis Approved date(Approved date should be between date range selected in slicer)
Solved! Go to Solution.
pls see the attachment below
Proud to be a Super User!
@ryan_mayu What if i want to calculate Count of Leads and Loans for 1 week prior to selected date in slicer.
Can you please help me in this?
There is some issue in this DAX
I want to filter
Count of loan approved basis Approve_Date
& Count of leads basis Lead_Created_Date
In Slicer, if I use Lead_Created_Date to select date range then only count of leads is showing correct
and if I use Approve_Date to select date range then only count of loans is showing correct
Not quite clear about your quesition. I think it will be better to use the date slicer from datetime table. not approve date or create date. Then use DAX to control the result based on approve date or create date.
Proud to be a Super User!
pls see the attachment below
Proud to be a Super User!
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 |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |