- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you, that worked right as i expected. When i place the summarized monthly view, it shows the way i needed:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 =
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
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.
Subject | Author | Posted | |
---|---|---|---|
05-30-2024 07:10 AM | |||
10-07-2024 10:05 AM | |||
02-04-2024 05:18 AM | |||
06-25-2024 08:25 AM | |||
06-04-2024 01:07 PM |
User | Count |
---|---|
13 | |
12 | |
11 | |
7 | |
7 |
User | Count |
---|---|
15 | |
13 | |
12 | |
9 | |
9 |