Greetings,
I have watched Sam’s few video clips on how to calculate cumulative month over month. I followed his instruction but the cumulative is not working when I plug it in the matrix. I am trying to calculate cumulative count of distinct subscribers from January thru December 2022 and filter that by Producer/Agent. Here are the steps I have taken:
First, I created a measure named New Subscriber = Calculate(DISTINCTCOUNTNOBLANK(Data[Subscriber ID]))
Second, I created the second measure to calculate cumulative for New Subscriber:
Cumulative New Enrollment Month =
CALCULATE(
[New Subscriber],
FILTER(ALLSELECTED(‘Data’),
MAX(‘Data’[Month]) >= ‘Data’[Month]
)
)
Then, I pluged in Month, New Subscriber, and Cumulative New Enrollment Month in a matrix (See Power BI file attched) and filtered by Producer A. The only accurate figure for cumulative is the January one! I can’t figure out what I am overlooking. Please help.
Update: I found out since I am using distinct count to calculate New Subscriber, the Cumulative calculation is actually working but it’s not the desirable outcome. What the cumulative count is doing is adding new subscribers to the pool from month to month. However, what I want is the the cumulative of distinct count of subscribers in each month. For example, in January, there are 104 unique subscribers. In February, there are 86 unique subscribers independent of those in January. Therefore, I need the cumulative for January = 104 and in February = 104+86 = 190.
@Paul.Gerber,
Thank you. I will add the Date table. Your cumulative formula worked except for January! While there are 104 unique subscribers in January 2022, the cumulative shows 112! any suggestion?
Hello. You have unique subscribers based on ID and then member count. So the countrows is counting every row for every column. I think it is adding the membercount associated with the member ID.
One possibility is that there are some Member IDs that appear in the January data but not in the cumulative data. This could happen if the data is filtered differently in the two cases, or if there are some records that are excluded from the cumulative data. To troubleshoot this, you can try to compare the Member IDs in the January data to those in the cumulative data to see if there are any differences.
Another possibility is that there are some duplicate Member IDs in the data, which could cause the COUNTROWS and DISTINCTCOUNT functions to return different results. To troubleshoot this, you can try to identify any duplicate Member IDs in the data and remove them or use a different method to count unique Member IDs, such as using a DISTINCT function with the SUMX function.
@Helal I would stop using CALCULATE function. I have. It will give you crazy numbers. I use CALCULATE but after watching Greg Deckler’s video going on now in the eDNA Summit for not using CALCULATE, Holy Moly it works.
Cumulative Subscribers =
VAR CurrentMonth = MAX(‘DateTable’[Date])
VAR _TABLE = FILTER(
ALLSELECTED(‘DateTable’),
‘DateTable’[Date] <= CurrentMonth)
RETURN
SUMX(_TABLE,[New Subscriber])
Thank you for updating us on your previous question. We’re glad to hear that you were able to identify the issue and find a solution.
As your initial question has been solved, we will be closing this thread. However, we understand that you have a follow-up question, and we’d be happy to help!
We kindly recommend that you create a new forum post for your follow-up question. This will ensure that your question is visible to all of our members and experts, allowing them to provide more accurate and targeted solutions.
Please be sure to include as much detail as possible about your updated situation and what you are trying to achieve. This will help our community better understand your question and provide more effective assistance.
Thank you for your participation in our forum, and we look forward to seeing your new post!
