Customer Debt Analytics - DAX practice - bonus exercise :)

Question

OK, here's a bonus exercise :)

I've got a Debt_Fact table (from the previous post : http://social.technet.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/a9904962-ed5d-4c7c-9ee4-88b506732e5c)

with snapshots of customer debts. There're multiple rows per customer per snapshot - no PK

Snapshot date column - Debt_Fact[RUN-DATE] is related to the Date Table - Calendar_Table for time-intelligence purposes

I want to build a report that contains the following measures - for any chosen month in filter (month only, not date, not year):

1. Debts_BeginningOfPeriod

2. !!! Debts_EndOfPeriod_SameCustomers - will calculate the end-of-period debts only for the customers that had debts in the beginning of period (the same customers that have the first measure non-empty)

3. Debts_EndOfPeriod_NewCustomers - will show new debts - customers that didn't have debts earlier, but do at the end-of-period

4. And, of course, Total_Debts_EndOfPeriod

I've built this report in SSRS, but I want to practice DAX and PIVOT - it seems to has been built for these kind of tricks...

Ideas?

Thank you

Michael Shparber

---

Michael