Area chart with salesytd

[Mantiero]

I create an area chart with mht sales for the years 2015-2016-SRtarget2016 (setted as 2017) divided by mth filtered by sales representatives.

I don't know why cooshing 2 or 3 years from the filter only first graph with mth sales function and the second one above appear only 1 salesytd.

[v-sihou-msft]

@Mantiero

In this scenario, I don't know what your date table looks like. Do you have Year 2015, 2016, 2017 in three different columns? 

Also please provide the formula for calculating the three YTD value. In DAX, when using function like TOTALYTD(), it's based on a dates column. If you group on "Month Of Year" and "Year", you can't apply time intellience on fact data.

Regards,

[Mantiero]

To compare 3 different values I used 3 different tables.

One is for 2015, one for 2016 and one is for target2016 (it is from an excel table that represents target for each sales representative), because if I'd used just 1 table with 1 coloumn for year, click on data filter years=2015+2016+2017) the value will be sum for each month.

To calculate YTD value I used these formulas:

Sales2015YTD = TOTALYTD(SUM('2015'[Sales 2015]);'Calendar'[Data])

Sales2016YTD = TOTALYTD(SUM('2016'[Sales 2016]);'Calendar'[Data])

SRtgt16YTD = TOTALYTD(SUM('SR target2016'[SR Target 2016]);'Calendar'[Data])

As you can see for date I used an extra table called "calendar" with all dates from 01/01/2015 to 31/12/2017 and link with relations with 3 tables.
For the table "Target16" I put coloum "data" from 01/01/2017 to 31/12/2017 to make different from 2016 sales table.

I hope to be clear

BR

[v-sihou-msft]

@Mantiero

In this scenario, you have three tables that each of them only them only covers 1 year dates. But you build the relationship to full date table which covers 3 years dates. Since apply 'Calendar'[Data] as filter in your YTD() calculation,when you call those the three YTD() measures and apply 'Calendar' field on X-axis , the 'Calendar' table context is limited to one Year date range only. So whenever you select more than one year, it will always return only one year YTD() calculation. 

It's really not a good practice to design your tables like that. It's better to creat a FactSales table containing all three years sales data. Also you need to create a date dimension with all dates and some other detail information columns like Year, Quarter, Month, MonthOfYear, etc. Then you can build the relationship between these two tables based on DateKey. And you just need one measure to calculate YTD(). To render your data in chart, you can put "MonthOfYear" on axis, and use "Year" as Legend.

Regards,