Cumulative count has blanks, is there a way to display running total instead of blanks?

[djayatillake]

Hi,

I've created a dataset which I'm using in a dashboard, it has for every loan we have deposited to a member:

MemberId

LoanId

CustomerLoanNumber (This is 1 for the Member's first loan, and 2 for the second...)

Category (Last loan, reborrowed after this loan)

MemberCohort (what YYYYMM did we board the customer)

The dashboard shows customers by loan number, split by category which works fine.

I was then asked to create a cumulative view of this, which seemed to work great with the formula below:

CumulativeUniqueMembers = CALCULATE(COUNT(Dataset[MemberId]), FILTER(ALL(Dataset[CustomerLoanNumber]), Dataset[CustomerLoanNumber] <= MAX(Dataset[CustomerLoanNumber])))

However, I found an issue when I filtered the chart to newer MemberCohorts: what I've found is that where a given category doesn't have any rows (members) for a specific customer loan number, the formula produces blanks for that category and customer loan number combination.

Is there any way that I could adjust the formula to show the running count when there are no rows for a particular category/customer loan number combination?

For example if for CustomerLoanNumber = 8 and category = 'LastLoan', the CumulativeUniqueMembers = 15, but there were no members who were on CustomerLoanNumber = 9 with category = 'LastLoan, would it be possible to display the previous CumulativeUniqueMembers from CustomerLoanNumber = 8 which was 15? 

I've spent hours trying to get this to work, none of the solutions I've seen out there seem to fit this case or haven't worked when I've attempted them. Would really appreciate any help or guidance.

Thanks

David

[waltheed]

I guess the issue is in your data model. You probably only have one flat table, Am I right?

Can you share your file in some way? 

[djayatillake]

Hi,

I've created a dataset which I'm using in a dashboard. It shows customer loans by no of loan they have taken, split by category. I was then asked to create a cumulative view of this, which seemed to work great with the formula below, but what I've found is that where a given category doesn't have any rows for a specific customer loan number, the formula produces blanks for that category and customer loan number.

Is there any way that I could adjust the formula to show the running count when there are no rows for a particular category/customer loan number combination?

CumulativeUniqueMembers = CALCULATE(DISTINCTCOUNT(DormancyCore[MemberId]), FILTER(ALL(DormancyCore[CustomerLoanNumber]), DormancyCore[CustomerLoanNumber] <= MAX(DormancyCore[CustomerLoanNumber])))