DAX Count occurrences by month on semi-additive table

[zax]

Hi all,

I'm having an issue that I think should be easy and I have searched but I don't know how to word it actually.

I have a 'Date' table and a 'Loan Balances' (fact) table joined on processing date. The fact table has two granularities, the last 60 days snapshot of daily loan balances (excluding weekends and holidays) and after 60 days it is rolled up to month ends only. 

What I'm trying to do is for the last 24 months, count how many loans were past due. For each month that has a loan past due count that as 1 (it was past due SOMETIME in that month). So if a single loan was past due for the last 12 months, I should have a count of 12. So when all loans are considered I need to count the occurrences and sum them up to get me the total count of loans past due in the last 24 months.

Below is a snippet of what I'm trying to achieve. 

There are two loans across in the sample, one that spans across historical month end data and into the daily data. the Yellow column would be what I want the measure to output. 

This gets me close but obviously adds the daily data within the month when it should only count once.

SUMX(
   DISTINCT(Loans[AccountLoanKey]),
   CALCULATE(
        COUNT(Loans[DPD 30 to 59])
    )
)  

I think I need to use SUMX or COUNTX, but with the daily vs end of the month data I'm not sure best approach here and I might be overthinking it.

Any help would be apperciated!

[amitchandak]

@zax ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Also, refer

https://www.sqlbi.com/articles/semi-additive-measures-in-dax/

[zax]

@amitchandak , I included a snippet of what I'm trying to do. I hope this make sense and works?