Forum Discussion

bonjourposte's avatar
bonjourposte
Helper V
1 year ago

Am I using ParallelPeriod right?

I'm differentiating between two time periods in my LOANHIST table- the current time period, and one month prior.  When comparing the two, taking into account a bunch of different filters, we can tell if a loan has been extended or renewed.  The date column we're using is "ACCOUNTING_DATE"- this lists the last day of every month.  

 

What I'm trying to build is something like this: 

...although we want one for the number of Renewals per month, and one for the number of Extensions.  My DAX isn't working- can anyone tell me why not?

 

This is what the model looks like: 

 

These are the columns in LOANHIST: 

 

And this is my DAX: 

NumRenewals5 =
VAR MatDate1 = MAX(LOANHIST[MAT_DATE])
VAR LoanStatus1 = MAX(LOANHIST[LOAN_STATUS])
VAR CurrentLoanTerm1 = MAX(LOANHIST[CURR_LOAN_TERM])
VAR CurrentAmzMos1 = MAX(LOANHIST[CURR_AMZ_MONTHS])

VAR MatDate2 = CALCULATE(COUNTX(LOANHIST, LOANHIST[MAT_DATE]),DATEADD(LOANHIST[ACCOUNTING_DATE],-1,MONTH))
VAR LoanStatus2 = CALCULATE(COUNTX(LOANHIST,LOANHIST[LOAN_STATUS]),DATEADD(LOANHIST[ACCOUNTING_DATE],-1,MONTH))
VAR CurrentLoanTerm2 = CALCULATE(COUNTX(LOANHIST, LOANHIST[CURR_LOAN_TERM]),DATEADD(LOANHIST[ACCOUNTING_DATE],-1,MONTH))
VAR CurrentAmzMos2 = CALCULATE(COUNTX(LOANHIST, LOANHIST[CURR_AMZ_MONTHS]),DATEADD(LOANHIST[ACCOUNTING_DATE],-1,MONTH))
VAR CurrStartDate2 = CALCULATE(COUNTX(LOANHIST,LOANHIST[CURR_START_DATE]), DATEADD(LOANHIST[ACCOUNTING_DATE],-1,MONTH))



RETURN
CALCULATE(
    COUNTROWS(LOANHIST),
        IF(
            AND(
                MatDate1<>MatDate2,
                LoanStatus1 <> "Commitment" &&
                LoanStatus2 <> "Cancelled"&&
                LoanStatus2 <> "PAIDINFULL"
            ),
        SWITCH(
            TRUE,
            CurrStartDate2 > MatDate1, "Renewal",
            CurrentLoanTerm2 <> CurrentLoanTerm1, CurrentAmzMos2 = CurrentAmzMos1, "Extension"
            )
        )
    )
 

 


Can someone get me onto the right track?

 

Thanks.

 

P.S. Here is some sample data: 

LoanAccounting DateLoan TermMat_DateCurr_AMZ_MonthsCurr_Start_DateYearMonthNameYearMoCURR_LOAN_TERM
130-Sep-22601-Jun-2330010/5/20172022SeptemberSeptember, 202260
231-Mar-21601-Feb-2530011/10/20172021MarchMarch, 202160
331-Dec-24601-Aug-2630011/1/20172024DecemberDecember, 202460
431-Jul-19601-Oct-233001/1/20182019JulyJuly, 201960
531-Oct-22601-Apr-243009/5/20182022OctoberOctober, 202260
631-May-18601-Jan-2230010/5/20182018MayMay, 201860
730-Jun-22601-Aug-2330011/1/20182022JuneJune, 202260
831-Aug-20601-Sep-243004/1/20032020AugustAugust, 202060
931-Jan-19601-Feb-2130012/1/20042019JanuaryJanuary, 201960
1030-Apr-19601-Jul-2530012/1/20182019AprilApril, 201960
1130-Sep-24601-Jul-273002/1/20192024SeptemberSeptember, 202460
1231-May-20601-Dec-243004/18/20032020MayMay, 202060

5 Replies