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)
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,