cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
corvandalfzen New Member
New Member

Wallets - Calculation of balances - Cashback balance

Hi PBI-lovers :slight_smile:

 

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”.

https://www.dropbox.com/s/2v5bqg5rd7h2l6i/190115%20Dummydata%20Balances%20Community.xlsx?dl=0

 

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?

  1. The calculation of the endbalances of the Cashbacks;
  2. 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

4 REPLIES 4
Community Support Team
Community Support Team

Re: Wallets - Calculation of balances - Cashback balance

@corvandalfzen,

 

It seems that you may just add custom column in Query Editor.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
corvada Regular Visitor
Regular Visitor

Re: Wallets - Calculation of balances - Cashback balance

@v-chuncz-msft

 

See pbix-file: https://www.dropbox.com/s/favgmmgcd56ts6x/190116%20PBI%20Dummy%20data.pbix?dl=0

I have made a few columns in the Query Editor.

 

The problem is the calculation of the endbalance of the cashbacks, see picture.

https://www.dropbox.com/s/iowq3v1a1q2nmk2/Cashback.png?dl=0

 

For example ClientID 699, 2018 September gives 0,30, in October there are no transactions, so the endbalance needs still to be 0,30 and not 0,00. Can you help me with the formula for calculating the correct endbalance?

 

Highlighted
corvada Regular Visitor
Regular Visitor

Re: Wallets - Calculation of balances - Cashback balance

@Zubair_Muhammad

 

Hello,

 

Can you help me?

 

Greetings from Holland,

 

Cor

 

corvada Regular Visitor
Regular Visitor

Re: Wallets - Calculation of balances - Cashback balance

Is there no one who can help me?????