Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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
@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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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] )
User | Count |
---|---|
134 | |
74 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |