Calculation of 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) 

[Anonymous · Accepted solution]

The numbers in Approved Loans Count and Count Leads Count do not follow from the description of the calculations and the initial conditions. You should make the description 1) absolutely clear and 2) show correct numbers because currently there's mistakes/contradictions in the calculations.

[Anonymous · Accepted solution]

DEFINE 

MEASURE Measures_[Lead Count] = 
// Count of LeadID from Loans Table 
// if Lead_Created_Date is between
// date selected in slicer. This condition
// is automatically satisfied due to the
// relationship between Loans and Created Dates.
DISTINCTCOUNT( Loans[LeadID] )


MEASURE Measures_[New Loan Count] = 
// Your description:
// 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
CALCULATE(
    DISTINCTCOUNT( Loans[LeadID] ),
    KEEPFILTERS( Loans[Funding Amount] > 0 ),
    FILTER(
        SUMMARIZE( 
            Loans,
            Loans[Approval Date],
            Loans[Lead Created]
        ),
        Loans[Approval Date] = Loans[Lead Created]
    )
)


MEASURE Measures_[Approved Loan Count] = 
// Your description:
// 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.
CALCULATE(
    COUNTROWS( Loans ),
    KEEPFILTERS(
        TREATAS(
            DISTINCT( 'Created Dates'[Created Date] ),
            Loans[Approval Date]
        )
    ),
    KEEPFILTERS( Loans[Funding Amount] > 0 )
)

[Anonymous · Accepted solution]

The numbers in Approved Loans Count and Count Leads Count do not follow from the description of the calculations and the initial conditions. You should make the description 1) absolutely clear and 2) show correct numbers because currently there's mistakes/contradictions in the calculations.