cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mohit_K
Helper I
Helper I

Calculation of Loans

I have two tables

Table 1

Admins Table 
Processingadmin_IDAdmin_Name
1Admin1
2Admin2
3Admin3
  

 

 

Table 2: Loan Table with Created date of last two months: April and May

Loans Table    
LeadIDLead_Created_DateFunding_AmountApprove_DateProcessingadmin_ID
3201/05/21500005/05/211
3302/05/21600006/05/212
3403/04/21003/04/213
3504/04/21700004/05/211
3605/05/21500005/05/212
3706/05/21200008/05/213
3807/05/21  3

 

I want output in following format

Required Output   
Admin_NameApproved Loans Count Leads CountNew Loan Count
Admin1210
Admin2221
Admin3010

 

Logics to be used:

   FromTo
Selected Date (Slicer)Using Lead_Created_Date01/05/2107/05/21

 

Approved Loans Count LogicCount 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 LogicCount of LeadID from Loans Table if Lead_Created_Date is between date selected in slicer
New Loan CountCount 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) 

2 ACCEPTED SOLUTIONS
daxer
Solution Sage
Solution Sage

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.

View solution in original post

daxer
Solution Sage
Solution Sage

daxer_0-1621277474423.png

daxer_1-1621277540143.png

daxer_2-1621277656253.png

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

 

View solution in original post

2 REPLIES 2
daxer
Solution Sage
Solution Sage

daxer_0-1621277474423.png

daxer_1-1621277540143.png

daxer_2-1621277656253.png

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

 

View solution in original post

daxer
Solution Sage
Solution Sage

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates