Identify customers who withdraw all money from a bank account

[av9]

Looking to flag customers who withdraw all their money from their bank account. The data looks like this with a balance of each account at the end of the month.

Customer	Account Type	Date	Balance
Customer A	Savings Account	31/03/2020	$1000
Customer B	Savings Account	31/03/2020	$2500
Customer B	Cheque Account	31/03/2020	$800
Customer C	Savings Account	31/03/2020	$500
Customer A	Savings Account	30/04/2020	$100
Customer B	Savings Account	30/04/2020	$25
Customer B	Cheque Account	30/04/2020	$0
Customer C	Savings Account	30/04/2020	$500

So in this situation, I would like to flag Customer B had a zero balance on their cheque account based on previous month having a positive value.

[Mariusz]

Hi @av9 

Try a column like below.

Column = 
VAR __previousMonthBalance =
    CALCULATE(
        SUM( 'Table'[Balance] ),
        ALLEXCEPT( 'Table', 'Table'[Customer], 'Table'[Account Type] ),
        PREVIOUSMONTH( 'Table'[Date].[Date] )
    ) > 0
RETURN 
     __previousMonthBalance && 'Table'[Balance] = 0

 the result will be

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[AllisonKennedy · Accepted solution]

@av9  Do you have a DimDate table? https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html

Once you have that, you can use a function like DATEADD(DimDate[Date], -1, Month) inside a MEASURE to get

Balance = SUM(Table[Balance])

Previous Month Balance= CALCULATE([Balance], DATEADD(DimDate[Date], -1, Month) )

Then create another MEASURE: 

Conditional Format = IF( [Previous Month Balance] > 0 && [Balance] =0 , 1, 0)

You can add this measure to the conditional formatting within the matrix to change color or add an icon or whatever your requirements are. https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

Let me know if you need more details on any of those steps

[amitchandak]

@av9 , both as new columns

last Month value = maxX(filter(table,[Customer] =earlier([Customer]) && [Account Type] =earlier([Account Type] )
&& [Date] < earlier([Date]) ),[Date] )

If([Balance] =0 && maxX(filter(table,[Customer] =earlier([Customer] ) && [Account Type] =earlier([ Account Type])
&& [Date] = earlier([last Month value]) ),[Balance] ) >0 , "Yes", "No")

In case there are more than one date in a month for a account type  then try

last Month value = maxX(filter(table,[Customer] =earlier([Customer]) && [Account Type] =earlier([ Account Type])
&& [Date] < earlier([Date]) && eomonth([Date],0) < eomonth(earlier([Date]) ,0 )),[Date] )