So i have basically done what is asked for in this post but as a bar chart:
https://community.fabric.microsoft.com/t5/Desktop/Problems-with-formula-for-creating-cumulative-coun...
This works for me
Now in my table are multiple subcategories for each entry which i also want to include in the visual.
For example if my table looked like this:
| Date | Total | MonthlyAmount | Subcategory | RuntimeMonths |
| Jan 22 | 40 | 4 | Dep 1 | 10 |
| Jan 22 | 36 | 3 | Dep 3 | 12 |
| Feb 22 | 55 | 5 | Dep 2 | 11 |
| Feb 22 | 40 | 4 | Dep 1 | 10 |
| Feb 22 | 36 | 3 | Dep 3 | 12 |
| Mar 22 | 55 | 5 | Dep 2 | 11 |
| Mar 22 | 40 | 4 | Dep 1 | 10 |
| Mar 22 | 36 | 3 | Dep 3 | 12 |
| Apr 22 | 40 | 4 | Dep 1 | 10 |
(The total is also the key attribute)
The visual i currently have would be this:
But i want to see which department is responsible for which part of the monthly amount. So i try to add the subcategory as legend and this happens:
Since some departments dont have any impact in april the acumulated values also dont get added to the sum in april.
How can i always keep the complete cumulative value in each month and also show the subcategories?
If anyone could help it would be greatly appreciated!
1 ACCEPTED SOLUTION
Yes this works but what if i have data that is in the future?
Currently the cumulative values are based on the DATESYTD function but what do i do as soon as i have data that is planned to be after the current year?
What problem are you facing because of dates that belong to the future years? Share the download link of the file, show the problem clearly and the expected result.
Sorry for not clarifying.
So im talking about the same table but the dates of would not be of - like in my earlier example - 2022 but in 2025. Like this:
| Date | Total | MonthlyAmount | Subcategory | RuntimeMonths |
| Jan 25 | 40 | 4 | Dep 1 | 10 |
| Jan 25 | 36 | 3 | Dep 3 | 12 |
| Feb 25 | 55 | 5 | Dep 2 | 11 |
| Feb 25 | 40 | 4 | Dep 1 | 10 |
| Feb 25 | 36 | 3 | Dep 3 | 12 |
| Mar 25 | 55 | 5 | Dep 2 | 11 |
| Mar 25 | 40 | 4 | Dep 1 | 10 |
| Mar 25 | 36 | 3 | Dep 3 | 12 |
| Apr 25 | 40 | 4 | Dep 1 | 10 |
And the table is still supposed to look like the one you created but now were talking about 2025.
Whenever i change the year of the dates to 2025 the cumulative T measure doesnt do anything. It stays empty.
I used your pbi file and only changed the dates and it doesnt work.
I can help further only when i get access to the file. Share the download link and the problem very clearly.
I recreated what you did in this file but with the changed date.
Now the edited version doesnt show the data for the acumulated values.
You can see what i mean here:
https://drive.google.com/file/d/1bHwAsEAM4Ry9AWiUIgcmSDF8iJdc8hNd/view?usp=drive_link
(Hope this link works)
Change the calendar table formula to
Calendar = CALENDAR(MIN(Data[Date]),MAX('Future Data'[Date]))Hope this helps.
Almost, this works when i have all the date in the same year what if one of the categories is in a different year?
Like this:
Then this happens:
How do i make it add up the same way?
Share the download link of the PBI file with that specific dataset and my measures already written. Show the problematic visual in that PBI file.
Hi,
Based on the data in the file, show the expected result in a Table format very clearly.
Sorry i messed up the data in the file i shared.
Here is a correct one:
https://drive.google.com/file/d/1bHwAsEAM4Ry9AWiUIgcmSDF8iJdc8hNd/view?usp=sharing
And this is what i need it to look like(with bars for each month instead of the three points of course):
So it always shows the accumulated monthly amount for each department and whenever there is an additional amount it just adds it to the amount of that department.
Also i might need it with a dynamic number of departments.
Thank you very much
The image i sent is not the finished graph but only an edited version of what i want it to look like. Thats why i said the 3 dots in the image are only because i didnt want to create a bar for the months between april 2025 and january 2026. For practical reasons but they should of course be in the final visual.
For Dep 3, information is available only for JFM 2026. There if i swap Year and Month on the X-axis, this is the image i get (with which i see nothing wrong)
Yes, but i need ALL of the monthly amount to be added up in every month after they are added.
In the ss you shared it resets after each year. I would like it to carry on the data from the previous years and departments.
Yes, thank you very much thats what i have been looking for.
