Previous Month Time Intelligence Measure resulting in wrong value or blank

Question

For sake of simplicity, I have a fact table called Loans within this fact table there are multiple dates associated with the loan and loan amount. For my initial Funded Loan Amount Measure I used the following Dax Equation:

LoanAmtTotal = CALCULATE(SUM(Loans[LoanAmt]),Loans[FundedDate] <> BLANK())

If the funded date is blank I do not want to count that value in the sum. Now that I have my LoanAmtTotal measure I want to use a time intelligence equation to calculate the previous Months funded loan amount. It should be noted I have a universal filter on all pages that narrows down the client for which the loans are funded by.

For my Previous Month Measure I used the following DAX equation:

Previous Month loan amount = CALCULATE(
    Loans[LoanAmtTotal],
    PREVIOUSMONTH(Dim_Date[Current_DT])
  )

Dim_Date[Current_DT] is my dates table and it is joined to the FundedDate on the Loans table. My date table is set as a date table so that shouldn't be an issue. When I insert the new time intelligence measure onto a table visual it is blank when I know for a fact that is not the case. When I change the Previous Month Loan Amount measure to use the Funded Date instead of Current_DT it populates a far too large of number. I made sure that the universal client filter was working.

I have tried duplicating the date dimension table. Redoing how I calculate loan amount. I created an extra column that flags whether funded date is null and used that as the filter context if that column is true then do not include. It should be noted that when I just put in the month as the column on a matrix visual and use loan amount as the values I do get the correct value for the loan amount of the previous month. It is only when I do the time intelligence that I run into problems.

Answer 1

I suspect you are missing a date context in your table visual.

PREVIOUSMONTH will use the first date found, and since you don't have a date context it will use the very first date in your date table, and since it is the first, there is no previous month. This also explains why you get a very large number when modified since it is returning the sum for all.

It should be noted that when I just put in the month as the column on a matrix visual and use loan amount as the values I do get the correct value for the loan amount of the previous month

Try the same with your [Previous Month loan amount] measure. Add it to the values of the Matrix above.