Wallets - Calculation of balances - Cashback balance


#1

Hi Sam and other 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”.
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?

  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


#2

190116 PBI Dummy data.pbix (190.8 KB)

The problem is calculating the endbalance of the cashbacks:

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?


#3

Is there someone who can help me? Please


#4

Good day Cor,

The way you can do this is running a cummulative total on the Total Cashback Balance Change.

I just have a question for you, I see in your excel file that for the Cashback Balance for the client 699 you are not taking the whole data set, correct?

Because I have run the cummulative total and I got a balance for the whole data of -170.42, and doing the cummulative totals after July, 2018 I got the balance you want.

Attached is the PBIX file with the measures Cashback Balance and Cashback Balance > July/2018

190116 PBI Dummy data.pbix (192.6 KB)


#5

Good day Jorge,

Thanks for your reply!,

There are 2 rules:

  1. Revenue Cashback: if Revenue < 0 then Revenue else 0 and
  2. Endbalance Cashback: If (startbalance CB + Revenue Cashback + Cashback) < 0 then 0 else (Startbalance CB + Revenue Cashback + Cashback)

In January 2015 the Total Revenue is - 9,06. The startbalance was 0, revenue cashback - 9,06 and Cashback 0.
0 - 9,06 + 0 = - 9,06 is less then 0 so cashback is 0.

On July 2018 is the calculation (see rule 2) 0 + 0 + 3,63 is more than 0 so the endbalance is 3,63.
Calculation August 2018: 3,63 - 3,62 + 2,41 = endbalance 2,42
Calculation September 2018: 2,42 - 2,12 + 0 = endbalance 0,30
Calculation October 2018: 0,30 + 0 + 0 = endbalance 0,30 and the endbalances for November and December 2018 and January 2019 should also be 0,30.

I’m taking the whole dataset from the beginning and not only a subset from after July 2018.

Maybe you can understand my challenge now and maybe you can help me to solve this challenge.

Thanks in advance,

Greeting from Holland,

Cor


#6

Hi @CorvanDalfzen,

Attached is the PBIX file with the New Measures table that contains the measures I use to filter and get the values.

Tell me if that’s what you needed.

190116 PBI Dummy data.pbix (200.6 KB)

Best regards,

Jorge Galindo


#7

Good Day Jorge,

Thanks for your reply!

Unfortunately it isn’t the solution I need but I think we are on the good way.

If we look at customer #699 than the endbalance with switch seems right but it isn’t calculating for 2019 January:

Furthermore, when I change the context to all customers, it isn’t calculating the Total endbalance for all customers. Customer 699 had an endbalance of 0,30 so the minimum endbalance of all customers should be at least 0,30:

Can you take another look at it?

Thanks,

Cheers,

Cor

190128 PBI Dummy data.pbix (209.9 KB)


#8

Hi Cor,

For the client 699, I checked the database and the data just gets to December 2018, let me check why is it that is brings up January 2019.

Also, just to make sure that the info is good with the other customers individually. Not the all customers context.

And just to make sure, you want to look at all the customers at the same time? or 1 by 1?

Best regards,

Jorge Galindo


#9

Cor,

The column that is bringing up January 2019 is the counter measure (diff months and the last date), here is a picture of 699 without the counters:

To see the whole client list and make it work in the filters, what you need to do is get the client ID without summarize in your visual like this:

image

That way we can get the Client ID context to the visual and not get the aggregates on the deltas from all the clientes and get the individual results for each client.

Best regards,

Jorge Galindo


#10

Hi Jorge,

I’ve tried your solution but can you tell me what I’m doing wrong?

When I select only customer 699 I will get the right answer, when I select all the customers I should expect atleast 0,30.

Cheers,

Cor


#11

Hi Cor,

From what I see in your pictures, seems like the context from the client_id is not being applied.

Here is the pbix file that I used:

190116 PBI Dummy data.pbix (200.7 KB)

Best regards,

Jorge Galindo


#12

Hi Jorge,

Thanks for your reply!

If I use your pbix-file it gives the right answer with the context on client_id:

When I remove the client_id from values, it gives me the wrong answer for the endbalance with switch:

Client_ID 699 has an endbalance of 0,30, so the total of all customers should be at least 0,30 and not 0.00.

Best regards,

Cor


#13

Hi @CorvanDalfzen,

That happens because the measure that uses the swith, use the last day as its counter, and for that to work I use your Endbalance Cashback with help for check the last date that has a value greater than 0.

Since your Endbalance Cashback with help has a condition that anything goes below 0 put a 0 on it, and without the Customer Context it returns a cummulative of all the customers, so that cummulative will be always below 0 and your Endbalance will be 0 and that’s why when you take out the customer context, all the Endbalance with switch go back to 0, since the greatest value it will get is 0.

Best regards,

Jorge Galindo


#14

Hi Jorge,

Thanks for your explanation, is it possible to do both? The correct answers for a single customer and for all customers?

Best regards,

Cor