Cumulative Count aggregated by month

[Anonymous]

Hello.

I am trying to plot a chart on Power BI where it shows the monthly cumulative distinct count (total number of costumers increasing by month).

I am using the attached table as my database and i have made the following measure:

Qtd Empresas ALL = calculate(distinctcount(Planilha1[Empresa ID]),filter(all(Planilha1[Data de Criação]),Planilha1[Data de Criação]<=max(Planilha1[Data de Criação])))

With this measure, iniatilly it works right as i expected:

But when instead of using the date field i change for the monthly view, suddenly it stop working:

Any ideas on how to solve this problem?

Closing Date	Costumer ID
01/01/2022	1
01/01/2022	2
05/01/2022	3
10/01/2022	4
01/02/2022	5
01/02/2022	6
01/02/2022	7
01/03/2022	8
01/03/2022	9
01/03/2022	10
01/03/2022	11
01/03/2022	12
01/04/2022	13
01/04/2022	14

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Add an index column.

Click Home>>Transform data>>Add column>>Index column>>From 1.

Then create a measure.

Qtd Empresas = calculate(COUNT(lanilha1[Costumer ID]),filter(all(lanilha1),lanilha1[Index]<=MAX(lanilha1[Index])))

If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Thank you, that worked right as i expected. When i place the summarized monthly view, it shows the way i needed:

[amitchandak]

@Anonymous , Formula should remain on date table and you should use date from date table for better result. Date of date table should join with your date

Qtd Empresas ALL = calculate(distinctcount(Planilha1[Empresa ID]),filter(all(Date[Date]),Date[Date]<=max(Date[Date])))

Month, Month Year, date in visual should come from date table

[Anonymous]

Hello Amit, thank you for your support.

After aplying the changes suggested by you, the result is not working. Look at the images below, could you provide a .pbix file so I can visualize it working with the table i have attached to the topic? thank you very much!

measure = 

Qtd Empresas ALL = calculate(distinctcount(Planilha1[Empresa ID]),filter(all(dCalendario[Data]),dCalendario[Data]<=max(dCalendario[Data])))

result (it is showing the all time total for every month);