Running total doesn't add up

[dmq147]

Hello, I'm really new to Power BI, I'm trying to display the running total of distinct values broken down by months. 

Here's what I have, it's some mock data, just some dates and email addresses. As you can see the email 'mpridmore9@ehow.com' is the only email repeated, it shows up 3 times across 2 months

I've added a measure

Distinct Email Count = DISTINCTCOUNT(MOCK_DATA[email])

and a running total measure

Distinct Email Running Total = 
CALCULATE(
	[Distinct Email Count],
	FILTER(
		ALLSELECTED('Calendar2'),
		'Calendar2'[YearMonth] <= MAX('Calendar2'[YearMonth])
	)
)

The table below seems to show the correct values for the "Distinct Email Count" measure, however for the "Distinct Email Running Total" measure, I'd expect the month 2 to be 9 and not 8. I'm guessing my formula is wrong as it seems to be giving me the entire distinct count in month 2 instead of (3+6)=9

Any help for a newbie would be greatly appreciated

[dmq147]

Thanks so much, just tested this now and it works but also it makes sense to me why it works

[Wilson_]

Perfect, thanks for letting me know, @dmq147. Happy to help with your problem and to explain why you're seeing the original result. 😄

[Wilson_ · Accepted solution]

Hi dmq147,

As you noted, one of the emails shows up in both months. Therefore, if you look at both months as a whole for your running total calculation for YearMonth of 2024-03, there are only 8 distinct emails. Your DAX is doing exactly what you're asking it to do.

What you're looking for though is to treat each month as a distinct entity, then add those together. Something like this should work:

Distinct Email Running Total = 
VAR SelYearMonth = MAX ( 'Calendar2'[YearMonth] )
VAR YearMonths =
FILTER (
   ALLSELECTED ( 'Calendar2'[YearMonth] ),
   'Calendar2'[YearMonth] <= SelYearMonth
)
VAR Result =
SUMX (
    YearMonths,
    [Distinct Email Count]
)

RETURN Result

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)