topic Re: Cumulative Count aggregated by month in DAX Commands and Tips

Cumulative Count aggregated by month

Anonymous — Mon, 02 May 2022 18:28:35 GMT

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

Re: Cumulative Count aggregated by month

amitchandak — Tue, 03 May 2022 01:25:55 GMT

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

Re: Cumulative Count aggregated by month

Anonymous — Tue, 03 May 2022 20:19:26 GMT

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);

Re: Cumulative Count aggregated by month

Anonymous — Thu, 05 May 2022 02:53:51 GMT

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.

Re: Cumulative Count aggregated by month

Anonymous — Thu, 05 May 2022 12:10:42 GMT

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