Reply
avatar user
Anonymous
Not applicable

Cumulative Count aggregated by month

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:
pedroguimaraes_0-1651515959423.png

 

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

pedroguimaraes_1-1651516020536.png

 

Any ideas on how to solve this problem?

 

Closing DateCostumer ID
01/01/20221
01/01/20222
05/01/20223
10/01/20224
01/02/20225
01/02/20226
01/02/20227
01/03/20228
01/03/20229
01/03/202210
01/03/202211
01/03/202212
01/04/202213
01/04/202214
1 ACCEPTED SOLUTION
avatar user
Anonymous
Not applicable

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.

vpollymsft_0-1651719100139.png

Then create a measure.

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

vpollymsft_1-1651719170118.png

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.

 

 

View solution in original post

4 REPLIES 4
avatar user
Anonymous
Not applicable

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.

vpollymsft_0-1651719100139.png

Then create a measure.

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

vpollymsft_1-1651719170118.png

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.

 

 

avatar user
Anonymous
Not applicable

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

pedroguimaraes_0-1651752634332.png

 

amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
avatar user
Anonymous
Not applicable

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);
pedroguimaraes_0-1651609131139.pngpedroguimaraes_1-1651609146676.png

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)