Forum Discussion
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 |
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.
4 Replies
- amitchandak
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
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);
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.
