SUMX and DISTINCCOUNT to sum over months get yearly count

[Anonymous]

I want a measure to count user subscriptions with DISTINCTCOUNT per month, and bring the sum of the monthly results per year, but not DISTINCTCOUNT per year. This is because there are users who subscribe month after month and I want to count them once each month, but maximum one time per month if they subscribed more than once during a month. 

Per user, there is a start date. I DISTINCTCOUNT the number of users who's start date falls between the min and max dates of the chosen period. This works fine for weeks and months, but not the year. I tried using SUMX to sum over the months, but it doesn't work. 

I have a Datetable, with no relationship to the subscription_activities table. 

I have a measure like this: 

subscriptions=

var _min= MIN('Date'[Date])

var _max= MAX('Date'[Date])

var akt = SUMX(VALUES('Date'[YearMonth]), CALCULATE(

DISTINCTCOUNT(subscription_activities[user_id]),FILTER( subscription_activities,

subscription_activities[start]<= _max &&

subscription_activities[start]>= _min 

)))

RETURN

IF(ISBLANK(akt), 0,akt)

And it returns a table like this 

subscriptions	Month Year (from Date table)
1000	January 2020
200	February 2020
100	March 2020
Total: 3300

And distinctcount result for the year is 1100, if we only count the user once per year. 

The monthly counts are correct. But the correct total should be 1300. What it shows now is DISTINCTCOUNT per year (1100) * number of months. So SUMX in my measure only calculates the same result per year, 3 times. But it should calculate the DISTINCTCOUNT per each month, then sum those values. 
Thank you

[Anonymous · Accepted solution]

It worked after I creted a new measure like this:

subscriptions_unique = 

Aktiveringer =

var _min= MIN('Date'[Date])

var _max= MAX('Date'[Date])

var akt = CALCULATE(

DISTINCTCOUNT(subscription_activities[user_id]),FILTER(subscription_activities,

subscription_activities[start]<= _max &&

subscription_activities[start]>= _min //started on that date

))

return IF(ISBLANK(akt), 0,akt)


Then used it in this measure:

Aktiveringer_unik_pm =

var akt = CALCULATE(SUMX(DISTINCT('Date'[Måned og År]),[Aktiveringer]))

RETURN

IF(ISBLANK(akt), 0,akt)