topic Re: How to check if customer maintained balance throughout the month in DAX Commands and Tips

How to check if customer maintained balance throughout the month

RickJani — Fri, 01 Jul 2022 14:14:31 GMT

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

Re: How to check if customer maintained balance throughout the month

Nathaniel_C — Fri, 01 Jul 2022 14:23:11 GMT

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

Re: How to check if customer maintained balance throughout the month

Nathaniel_C — Fri, 01 Jul 2022 14:30:58 GMT

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

Re: How to check if customer maintained balance throughout the month

RickJani — Fri, 01 Jul 2022 14:45:06 GMT

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

Re: How to check if customer maintained balance throughout the month

Anonymous — Tue, 05 Jul 2022 06:29:55 GMT

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

Re: How to check if customer maintained balance throughout the month

RickJani — Tue, 05 Jul 2022 13:02:45 GMT

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.

Re: How to check if customer maintained balance throughout the month

Anonymous — Wed, 06 Jul 2022 02:16:25 GMT

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.

Re: How to check if customer maintained balance throughout the month

RickJani — Wed, 06 Jul 2022 18:00:03 GMT

Thanks for your help Liu.