cancel
Showing results for
Did you mean:
Helper I

## Calculation of Loans

I have two tables

Table 1

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

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)

2 ACCEPTED SOLUTIONS
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.

Solution Sage

``````DEFINE

// Count of LeadID from Loans Table
// date selected in slicer. This condition
// is automatically satisfied due to the
// relationship between Loans and Created Dates.

MEASURE Measures_[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
CALCULATE(
KEEPFILTERS( Loans[Funding Amount] > 0 ),
FILTER(
SUMMARIZE(
Loans,
Loans[Approval Date],
),
)
)

MEASURE Measures_[Approved 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.
CALCULATE(
COUNTROWS( Loans ),
KEEPFILTERS(
TREATAS(
DISTINCT( 'Created Dates'[Created Date] ),
Loans[Approval Date]
)
),
KEEPFILTERS( Loans[Funding Amount] > 0 )
)``````

2 REPLIES 2
Solution Sage

``````DEFINE

// Count of LeadID from Loans Table
// date selected in slicer. This condition
// is automatically satisfied due to the
// relationship between Loans and Created Dates.

MEASURE Measures_[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
CALCULATE(
KEEPFILTERS( Loans[Funding Amount] > 0 ),
FILTER(
SUMMARIZE(
Loans,
Loans[Approval Date],
),
)
)

MEASURE Measures_[Approved 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.
CALCULATE(
COUNTROWS( Loans ),
KEEPFILTERS(
TREATAS(
DISTINCT( 'Created Dates'[Created Date] ),
Loans[Approval Date]
)
),
KEEPFILTERS( Loans[Funding Amount] > 0 )
)``````

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.

Announcements

#### 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.