Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

SUMX and DISTINCCOUNT to sum over months get yearly count

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 
subscriptionsMonth Year (from Date table)
1000January 2020
200February 2020
100March 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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)

 

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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)

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.