Creating a cumulative bar chart with additional categories

[mophi]

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!

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[mophi]

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?

[Ashish_Mathur]

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.

[mophi]

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.

[Ashish_Mathur]

My solution should work irrespective of the year of the entries in the Date column.

[mophi]

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.

[Ashish_Mathur]

I can help further only when i get access to the file.  Share the download link and the problem very clearly.

[mophi]

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)

[Ashish_Mathur]

Change the calendar table formula to

Calendar = CALENDAR(MIN(Data[Date]),MAX('Future Data'[Date]))

Hope this helps.

[mophi]

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?

[Ashish_Mathur]

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.

[mophi]

https://drive.google.com/file/d/1bHwAsEAM4Ry9AWiUIgcmSDF8iJdc8hNd/view?usp=drive_link

[Ashish_Mathur]

Hi,

Based on the data in the file, show the expected result in a Table format very clearly.

[mophi]

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

[Ashish_Mathur]

I do not see 3 dots there.  Everything seems fine to me.

[mophi]

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.

[Ashish_Mathur]

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)

[mophi]

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.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

[mophi]

Yes, thank you very much thats what i have been looking for.