Hi Sam and other PBI-lovers

I’ve been struggling a couple of days with the calculation of walletbalances in Power BI.

We are working with customers who have a so called “Wallet”.

As an attachment you will find dummy data of 5 customers.

190115 Dummydata Balances Sam.xlsx (69.7 KB)

In the first worksheet you will find all the data:

Timeperiod = Year/Month

ClientId = ClientID

Balance_start_period = The startbalance of the wallet

Revenue - Purchases, normally “Balance Out” but it is possible to have a “Balance In” if the purchase has failed.

Payments = Payments (= Balance In)

Cashback = In certain circumstances a customer receives a cashback (=Balance In).

Balance_end_period = The endbalance of the wallet

Worksheet 2 t/m 6: calculations in Excel of the different customers.

Additional information for Cashback:

Revenue Cashback: if Revenue < 0 then Revenue else 0.

Endbalance Cashback: If (startbalance CB + Revenue Cashback + Cashback) < 0 then 0 else (Startbalance CB + Revenue Cashback + Cashback)

Extra:

It is possible to have a wallet < 0, those are Accounts Receivable Customers.

Can someone help me to do the Excel-calculations in Power BI?

- The calculation of the endbalances of the Cashbacks;
- The overall endbalance contains the amount of Cashbacks (point 1) and an amount of “non-Cashback”.

I would like to see the overall amount that is separated between these 2 items.

Thanks in advance,

Greetings from Holland,

Cor