Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.
Hi,
I'm trying to find the customers that I lost from the previous month.
So far I've been able to calculate it individually but I'm stuck with the subtotals.
The model is a single table inndicating if a customer is active or not. The expected outcome is the measure "Lost".
The measure for past months looks like this (works fine):
The measure for lost looks like this (the subtotals don't work fine):
Solved! Go to Solution.
Hey @APR92, there is probably a much cleaner way to do this, but try this:
Lost2 =
VAR selectedDate =
SELECTEDVALUE(Consulta1[Date])
VAR selectedCategory =
SELECTEDVALUE(Consulta1[Category])
VAR tbl =
ADDCOLUMNS (
ADDCOLUMNS (
Consulta1,
"Active Last Month",
MAXX (
FILTER (
ALL ( Consulta1 ),
Consulta1[Date] < EARLIER(Consulta1[Date])
&& (
Consulta1[Category] = EARLIER(Consulta1[Category])
|| ISBLANK ( EARLIER(Consulta1[Category]) )
)
),
Consulta1[Active]
)
),
"Lost",
SWITCH (
TRUE,
ISBLANK ( [Active Last Month] ), BLANK (),
[Active Last Month] < Consulta1[Active], 0,
[Active Last Month] - Consulta1[Active]
)
)
VAR lost =
SUMX (
FILTER (
tbl,
Consulta1[Date] = selectedDate
&& (
Consulta1[Category] = selectedCategory
|| ISBLANK ( selectedCategory )
)
),
[Lost]
)
RETURN lost
@giammariam Hello,
First of all apologizes if the logic is not clear. Let me clarify:
I would like to determine how many customers I've lost this month versus the ones that I had the previous month only.
This means that any previous customer I had older than the previous month does't count. The comparison is month by month.
For example, I have to compare the customers that were active in february with the customers that were active in january.
The expected result right now is fine with the user granularity but the montly subtotals are not showing a proper value:
As you can see the subtotal "2023-02" is correct as is adding up user B and E that are lost.
The subtotal 2023-03 is wrong as is 0 and is not adding up the user C that is los (highlighted in green).
PS: Don't worry about the grand total as it is not needed in this measure.
Thank you very much, I highly appreciate your support! 🙂
Hey @APR92, there is probably a much cleaner way to do this, but try this:
Lost2 =
VAR selectedDate =
SELECTEDVALUE(Consulta1[Date])
VAR selectedCategory =
SELECTEDVALUE(Consulta1[Category])
VAR tbl =
ADDCOLUMNS (
ADDCOLUMNS (
Consulta1,
"Active Last Month",
MAXX (
FILTER (
ALL ( Consulta1 ),
Consulta1[Date] < EARLIER(Consulta1[Date])
&& (
Consulta1[Category] = EARLIER(Consulta1[Category])
|| ISBLANK ( EARLIER(Consulta1[Category]) )
)
),
Consulta1[Active]
)
),
"Lost",
SWITCH (
TRUE,
ISBLANK ( [Active Last Month] ), BLANK (),
[Active Last Month] < Consulta1[Active], 0,
[Active Last Month] - Consulta1[Active]
)
)
VAR lost =
SUMX (
FILTER (
tbl,
Consulta1[Date] = selectedDate
&& (
Consulta1[Category] = selectedCategory
|| ISBLANK ( selectedCategory )
)
),
[Lost]
)
RETURN lost
@APR92, this is because of the desired behavior was unclear. Overall for 2023-03 you had 4 currently active where you only had 3 active the month before. Since you still ended up with a total of more active than you did the previous month, the [Lost] metric is indicating that overall you didn't lose any for that month. Sounds like instead you want a total of any lost, regardless of what the overall picture is for that month. Now that I know that this is the desired behavior I'll try to get this implemented as soon as I get a chance.
I'm super stuck with it. Help please :$
I've played a little bit the past period measure, but the subtotals still wrong...
@giammariam 's response has the exact same issue with the lost's subtotals.
@giammariam Hello, the subtotal for the period 2023-03 is not correct. The user granularity results is fine.
Thank you a lot for your time!
Hey @APR92 try this. The negatives under the Lost field made it a bit confusing. I made some assumptions with the business rules:
Let me know if any of these rules are incorrect.
Past Month =
VAR var1 =
CALCULATE(
SUM(Consulta1[Active]),
PREVIOUSMONTH(Consulta1[Date])
)
VAR maxDate = MAXX(
ALLSELECTED(Consulta1),
Consulta1[Date]
)
VAR sumMinusMaxDate = SUMX(
FILTER(
ALLSELECTED(Consulta1),
Consulta1[Date] <> maxDate
),
Consulta1[Active]
)
RETURN
IF(
COUNTROWS(ALLSELECTED(Consulta1)) = COUNTROWS(Consulta1),
sumMinusMaxDate,
var1
)
Lost =
VAR sumActive = SUM(Consulta1[Active])
VAR var1 = SWITCH(
TRUE,
ISBLANK([Past Month]), BLANK(),
sumActive<=[Past Month], [Past Month]-sumActive,
sumActive>[Past Month], 0
)
RETURN
var1
If this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the September 2025 Power BI update to learn about new features.