Cumulative Count Decreases?

[benjamin_sasin]

Hi,

I'm a little confused after running a cumulative count, to find that the number is decreasing at some point:

The formula is:

Cumulative Count = CALCULATE(COUNT('Admissions'[email]),FILTER(ALLSELECTED(Admissions),Admissions[startedApplicationAt] <= Max('Admissions'[startedApplicationAt])))

The numbers are fine initially:

But when I filter one category, the number is decreasing in Jan 2020.

I can't wrap my mind around how that's even possible. Any clues?

[dax]

Hi benjamin_sasin, 

Yes, if possible, could you please inform me more detailed infromation for me (such as your sample data or your pbix file)? In addition, you also could check whether the category has negative value in it.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[benjamin_sasin]

Hi I'm sorry, for data privacy reasons I cannot share the pbix file. 

There are no negative categories: it is counting text (emails) against dates.

As with sample data shown above, there is a number of anomalies, where the cumulated count is going down instead of increasing. See dates 26 December or 31 December for example.

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.

Is any reason to use ALLSELECTED, can we try without that

Cumulative Count = CALCULATE(COUNT('Admissions'[email]),FILTER((Admissions),Admissions[startedApplicationAt] <= Max('Admissions'[startedApplicationAt])))

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[benjamin_sasin]

Hi,

I have used the ALLSELECTED() based on examples in this forum. If I remove ALLSELECTED() then the count isn't cumulative anymore:

(No longer able to insert screenshots for some reason).

As for the data sample: here it is (cleaned up) the column to the right I reconstructed from date parts on the left to order the data. As you can see, the cumulative count drops on January 2020 and there's also another anomaly on december 26 where the count drops to 13 for some reason.

latest_stage_date - Year	latest_stage_date - Month	latest_stage_date - Day	Cumulative Count	user_id	Country	Academic_Year	program	program_order	application_stage_sort_order	datefromparts
2019	April	4	1	3029	Thailand	2020	1.2	1	2	4-Apr-2019
2019	May	22	2	3162	India	2020	4	4	2	22-May-2019
2019	June	14	3	3222	Bangladesh	2020	1.2	1	2	14-Jun-2019
2019	July	3	4	3278	Tanzania	2020	2	2	2	3-Jul-2019
2019	July	19	5	3317	China	2020	1.2	1	2	19-Jul-2019
2019	July	21	6	3325	Palestine	2020	2	2	2	21-Jul-2019
2019	August	24	7	3432	Thailand	2020	3	3	2	24-Aug-2019
2019	September	3	8	3467	Denmark	2020	3	3	2	3-Sep-2019
2019	September	15	9	3529	Indonesia	2020	1.2	1	2	15-Sep-2019
2019	September	23	10	3591	Myanmar	2020	1.1	1	2	23-Sep-2019
2019	September	26	11	3616	Indonesia	2020	3	3	2	26-Sep-2019
2019	September	26	12	3624	Pakistan	2020	2	2	2	26-Sep-2019
2019	October	3	14	3667	Taiwan	2020	3	3	2	3-Oct-2019
2019	October	11	15	3698	Thailand	2020	1.2	1	2	11-Oct-2019
2019	October	14	16	3706	Thailand	2020	2	2	2	14-Oct-2019
2019	October	17	18	3722	Malaysia	2020	4	4	2	17-Oct-2019
2019	November	10	20	3808	China	2020	3	3	2	10-Nov-2019
2019	November	18	22	3876	Thailand	2020	1.1	1	2	18-Nov-2019
2019	December	18	24	3866	Thailand	2020	3	3	2	18-Dec-2019
2019	December	18	25	3850	Papua New Guinea	2020	1.1	1	2	18-Dec-2019
2019	December	20	23	3998	Thailand	2020	3	3	2	20-Dec-2019
2019	December	23	21	3860	Thailand	2020	1.1	1	2	23-Dec-2019
2019	December	26	13	3638	Nigeria	2020	2.2	2	2	26-Dec-2019
2019	December	27	26	4035	Thailand	2020	3	3	2	27-Dec-2019
2019	December	28	17	3707	Thailand	2020	3	3	2	28-Dec-2019
2019	December	29	28	4055	Cambodia	2020	1.2	1	2	29-Dec-2019
2019	December	31	19	3757	Thailand	2020	9	5	2	31-Dec-2019
2020	January	2	27	3921	Thailand	2020	1.1	1	2	2-Jan-2020

[benjamin_sasin · Accepted solution]

Okay I figured it out. As usual it as a silly mistake on my part.

As I observed a mismatch between date a cumulative count, I realise the name fo the date in the formula [startedApplicationAt] wasn't the same as in the report [latest_stage_date], which is the one in the axis of the visual.

So obviously the cumulation wasn't synced with dates... *facepalm*.

Sorry for wasting your time and thanks for sharing your neurons. I probably wouldn't have seen it alone.