How to check if customer maintained balance throughout the month

[RickJani]

Hello folks,

I want to check if customer maintained > 0 balance every day for 3 months so bank won't charge them any service fee. 

If any day within 3 months, customer balance was zero, then service fee is charged.

Can someone help me to find out how to accomplish this in power Bi?

Thanks

Rick

[Anonymous]

Hi  @RickJani ,

"balance is not zero but service fee is charged." Is there any calculation formula? You can express the result in the form of pictures, we can help you better.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RickJani]

Thanks for your help Liu.

[Anonymous · Accepted solution]

Hi  @RickJani ,

I add some data:

Here are the steps you can follow：

1. Create measure.

Measure =
MAX('Table'[Balance]) - MAX('Table'[Amount])

Flag =
var _today=TODAY()
var _mindate=DATE(
    YEAR(_today),MONTH(_today)-3,DAY(_today))
return
IF(
    MAX('Table'[Date])>=_mindate&&MAX('Table'[Date])<=TODAY()&&[Measure]>0,"NO","service fee is charged")

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[RickJani]

Many thanks Liu. I really appreciate your effort. It works well except in last 2 rows where balance is not zero but service fee is charged.

[RickJani]

Many thanks Nathan! That was quick!

I have data like this...I need to group by date by customer and check balance if it is more than zero

Date	Cust ID	transaction	Amount	Balance
01-Jan	cust 1	debit	100	600
01-Jan	cust 2	credit	200	800
03-Jan	cust 3	debit	1800	2900
04-Jan	cust 1	credit	300	900
06-Jan	cust 4	credit	6000	18500
06-Jan	cust 2	debit	900	2000
08-Jan	cust 2	debit	700	100

[Nathaniel_C]

Hi @RickJani ,

If you have a column with the daily balance, you might create a column that is Fee due= If(table[Balance]>0,0,1) then your measure might be Penalty = If(Sum(table[Fee due]>0,"Penalty is due", "No Penalty")
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @RickJani ,

Please give us more data or share your .pbix with us.

If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel