Forum Discussion
Running total doesn't add up
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?)
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?)