Am I using ParallelPeriod right?

[bonjourposte]

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: 

Loan	Accounting Date	Loan Term	Mat_Date	Curr_AMZ_Months	Curr_Start_Date	Year	MonthName	YearMo	CURR_LOAN_TERM
1	30-Sep-22	60	1-Jun-23	300	10/5/2017	2022	September	September, 2022	60
2	31-Mar-21	60	1-Feb-25	300	11/10/2017	2021	March	March, 2021	60
3	31-Dec-24	60	1-Aug-26	300	11/1/2017	2024	December	December, 2024	60
4	31-Jul-19	60	1-Oct-23	300	1/1/2018	2019	July	July, 2019	60
5	31-Oct-22	60	1-Apr-24	300	9/5/2018	2022	October	October, 2022	60
6	31-May-18	60	1-Jan-22	300	10/5/2018	2018	May	May, 2018	60
7	30-Jun-22	60	1-Aug-23	300	11/1/2018	2022	June	June, 2022	60
8	31-Aug-20	60	1-Sep-24	300	4/1/2003	2020	August	August, 2020	60
9	31-Jan-19	60	1-Feb-21	300	12/1/2004	2019	January	January, 2019	60
10	30-Apr-19	60	1-Jul-25	300	12/1/2018	2019	April	April, 2019	60
11	30-Sep-24	60	1-Jul-27	300	2/1/2019	2024	September	September, 2024	60
12	31-May-20	60	1-Dec-24	300	4/18/2003	2020	May	May, 2020	60

[lbendlin]

PARALLELPERIOD is one of the sneaky ones. It returns the ENTIRE period, if you want it or not.  Better use explicit DATEADD.

PARALLELPERIOD function (DAX) - DAX | Microsoft Learn

[bonjourposte]

DATEADD needs continguous dates, which I don't have in my AccountingDate column.  

[Anonymous]

Hi,@bonjourposte 
Can you tell me if your problem is solved? If yes, please accept lbendlin's reply  as solution.

Best Regards,

Leroy Lu

[bonjourposte]

Not yet.  he had mentioned using DATEADD, but to use my date calendar instead of my list of dates in the table.  I'm not entirely sure that's the solution at this point.

[lbendlin]

Ah but you need to have a calendar table in your data model anyway 🙂