Calculate End of Month Balance

[BruceK57]

I have 20 vendors. Each vendor has a customer base. I want to calculate the customer base at the end of the month in Power Pivot / DAX. The calculation is:

Beginning of this month customer count (same last month end of month customer count)
Plus New Customers
Minus Dropped Customers
Equals this month end of month customer count

I think the way to do it is to have a calculated column in the Power Pivot table called EOM Count. The formula in this column would be:

EOM Count from the preceding row plus adds from current row minus drops from current row.

I haven't worked out the formula, I want to be sure I have the concept correct first.

Also, since I have multiple vendors, each with their own ID, I should be able to add the EOM Count field in my pivot table, then filter it in the pivot table when I add a vendor column. Is this correct?

Thank you,
Bruce

[v-shex-msft]

Hi @BruceK57,

According to your description, you want to get the each vendor's monthly customer count change, right?

If this is a case, you can use current vendor, month, year to filter table and get the count.

Sample: measure

Monthly Count=
var currVendor= LastNoBlank(Table[Vendor ID],[Vendor ID])
var currDate= Max(Table[Date])
Return
Calculate(CountA(Table[Custom]),
Filter(ALL(Table),
Table[vendor ID]=currVendor&&
Year(Table[Date])=Year(currDate)&&
Month(Table[Date])=Month(currDate)))

BTW, your logic suitable deal with the table which store the customers count with running total.

Current Month Count = EOM - Previous EOM

If above formula not suitable for your requirement, please share us a sample file to test.

Regards,

Xiaoxin Sheng

[BruceK57]

Thank you for your reply.  This looks like a simple answer, but I will have to study it to see how I can apply it to my model.

Since I have a meeting in a few days, for now I have created an Excel table that calculates the month end number.  I am bringing this table into my data model and will use it for the moment.

After the meeting, if the client decides to continue, I'm definitely going to have to learn new ways to make this better and may in fact offer a file for you to review.

Thank you,

Bruce

[BruceK57]

I have 20 vendors.  Each vendor has a customer base.  I want to calculate the customer base at the end of the month in Power Pivot / DAX.  The calculation is:

Beginning of this month customer count (same last month end of month customer count)

Plus New Customers

Minus Dropped Customers

Equals this month end of month customer count

I think the way to do it is to have a calculated column in the Power Pivot table called EOM Count.  The formula in this column would be:

EOM Count from the preceding row plus adds from this row minus drops from this row.  

I haven't worked out the formula, I want to be sure I have the concept correct first.

Also, since I have multiple vendors, each with their own ID, I should be able to add the EOM Count field in my pivot table, then filter it in the pivot table when I add a vendor column.  Is this correct?

Thank you,

Bruce