Area Chart to show accumulated values for each year

[AZL]

Hello Everybody

I need your helps guys with the following:

I have the following dataset:

I want to create an area chart to show the accumulated value for each year..so The output chart will be like this

2015 = 4

2016 = 9

2017 = 15

2018 = 22

The default area chart will show only the individual value for each year not the accumulative.

How can I solve this?!!

Thanks

[Ashish_Mathur]

Hi,

Ensure that the Date column in your dataset carries actual date entries.  Create a Calendar Table and build a relationship from the Date column of your dataset to the Date column of the Calendar Table.  In the Calendar Table, create a Year column with this calculated column formula Year = Year(Calendar[Date]).  In your visual, drag Year from the Calendar Table.

Write these measures

Total = SUM('Table1'[Data])

Total since inception = CALCULATE([Total],DATESBETWEEN('Calendar'[Date],MINX(ALL(Calendar),'Calendar'[Date]),MAX('Calendar'[Date])))

Hope this helps.

[AZL]

@Ashish_Mathur

Thank yo for your reply.

Excuse me I'm still new to the software that's why I still can't figure out some of the points you mentioned in your reply.

Where should I write the measures? and how can I create a calender table?

I will appreciate it if you attach a file or something for more illustration.

One more question, in my actual dataset there are some rows without a date "null" which I dont want to be included in the calculations.Is this solution still valid in this case or I have to add something in order to exclude items without date?

Thank you again.

[v-jiascu-msft]

Hi @AZL,

Your date column has nulls. That's why a calendar table is needed. Please share a dummy sample. A simple way to create a calendar table is like below.

Calendar = Calendarauto()

Best Regards,

[AZL]

Greetings,

@Ashish_Mathur @v-jiascu-msft

 Unfortunately I dont know how to attach a file in here.

but here's a sample of my dataset and the resulted chart:

 

What I'm looking for is to get the accumulated value for each year without taking into consideration "null" cells.

Thanks,

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[AZL]

@Ashish_Mathur

That's exactly what I want.

Thank you so much for your help.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

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