My problem is twofold, first, for each customer, I need to sum all amounts with MRC in the Product Type. I did this using the attached data and this measure:
MRC Revenue - Current Month =
SUM ( Details[Revenue] ),
FILTER ( Details, FIND ( “MRC”, Details[Product_Type], 0 ) )
Next, I need to show any customers where total MRC went from a positive or negative amount to $0. In other words, if the customer was billed last month or credited last month and now they have no revenue, then they must have canceled. We want to see all customers who canceled in the current month as selected using a month/year slicer.
To do this part, I used the ‘Filters on This Visual’ and set MRC Prior Revenue is not 0 and MRC Revenue - Current Month is 0.
The measure for MRC Prior Revenue is MRC Revenue - Prior Month = CALCULATE([MRC Revenue - Current Month],DATEADD(‘Date’[Date],-1,MONTH))
This works except, my result is showing customers where the individual service types went from having revenue to $0 revenue. For example, if “Rzcurrong - MRC - RzachUC” had a balance and now has a zero, my result shows this. I need the result to show only when the TOTAL OF ALL revenue that has MRC in the product type was positive or negative and is now $0.
powerbi post data.xlsx (21.5 KB)