How to view this year dynamic per months? (Comulative Total per months)

[gerasimovav]

Hello colleagues!

I often have to do similar task.

For example, we register incidents every day.

I can count incident by countrows measure, then put diagram: X axis is month and Y axis is amount of incident. 

For example, 5 in January, 3 in February, 2 in March...

There is no problem if I want to view amount of incident every months.

But if i want to view number of incident from the start of the year till the end of every month:

for example January - 5, February 8 (5+3), March 10 (5+3+2) - it is very difficult.

How can I plot this graph?

[v-juanli-msft]

Hi @gerasimovav

As tested, Ashish_Mathur's reply can lead you directly to the results you wanted.

While Greg_Deckler's link provides the method to solve this kind problem, after reading , you may learn a lot and implement it in your scenario.

If you'd like to put the end of every month as Axis, you could create a column in the "calendar" table, i make a test as below.

create a column
end of month = CALCULATE(MAX('calendar'[Date]),ALLEXCEPT('calendar','calendar'[month]))

create a measure
Measure = COUNTROWS(Sheet1)
Measure 3 = CALCULATE([Measure],FILTER(ALL('calendar'),'calendar'[month]<=MAX('calendar'[month])))

Best Regards

Maggie

[gerasimovav]

Thank you very much!

All of the answers are good, but @Ashish_Mathur's answer is the most convenient way for me.

[Ashish_Mathur]

You are welcome.

[gerasimovav]

@Ashish_Mathur thank you again.

I've deployed your way, it works. But there is one moment. The result graph doesn't depend on filters. If we have different types of incident, for example high, medium and low. If I put the slicer for this types and choose high, montly graph shows dynamic of high incidents per month, but total year graph still shows all of incidents...

[Ashish_Mathur]

It should work.  Share the link from where i can download your PBI file.

[gerasimovav]

@Ashish_Mathur https://arvatorus-my.sharepoint.com/:u:/g/personal/anton_gerasimov_arvato_ru/EVGt3AWd1ZNDlLHF894MrmE...

[Ashish_Mathur]

Hi,

Download the PBI file from here.  Get the months in the first table in order.

[gerasimovav]

@Ashish_Mathur thank you a lot!!!

Was my mistake using Column instead of measure?

[Ashish_Mathur]

Yes and also the relationship.

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and write the following calculated column formulas there.

Year=Year(Calendar[Date])

Month=FORMAT(Calendar[Date],"mmmm")

Build a relationship from the Date column of the base Data Table to the Date column of the Calendar Table.  In your Table visual, drag the Year and Month from the Calendar Table.  Write these measures:

Incidents = COUNTROWS(Data)

Incidents YTD = CALCULATE([Incidents],DATESYTD(Calendar[Date],"31/12")

Your Months on the x-axis will not be in order.  Just Google for how to get months in order in PowerBI desktop and yo will be able to see the simple procedure.

Hope this helps.

[Greg_Deckler]

There are Running Total Quick Measures. Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...