Count of Leads and Loans

[mohit_K]

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) 

[mohit_K]

@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?

[mohit_K]

@ryan_mayu 

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

[ryan_mayu]

@mohit_K 

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.

[ryan_mayu · Accepted solution]

@mohit_K 

pls see the attachment below