YTD Linechart comparison of different years

[Paddhof1984]

Hello,

I do have a Problem:

 

In this line chart there should be shown the cumulated turnover for the years 2016 and 2017. I have created a measure for the cumulated turnovers per year, because I want them to be shown month by month. Now in this case somehow only the cumulated turnover for 2017 is shown in the chart, the cumulated turnover for 2016 isn't even shown on the chart. Anyone a clue why this happens and why the cumulated turnover for 2016 isn't shown?

[Paddhof1984]

@v-yuezhe-msft

the drill-down button isn't working as expected. Like I said, I am getting dots for every single month of the years 2016 and 2017 for the cumulated turnover. But what I need instead are lines which connect every single month of the specific year.

[v-yuezhe-msft]

@Paddhof1984,

You would need to put Year in the first level when creating Line chart as shown in the screenshot below, otherwise you would get dots when you turn on drill down as the data for single month of different years is not linear, it is not possible to show it as line.


If you need to put Month in Year in the first level, please append the two tables in a single table, then create YTD, which is described in my first reply.

Regards,
Lydia

[Paddhof1984]

@v-yuezhe-msft So there is no possibility to compare both cumulated turnovers month by month with the line chart, so that both turnover values increase for every single month and are, I should say" laid above each other?

[v-yuezhe-msft]

@Paddhof1984,

The only way is to append the two table in a single table, then create YTD and create Line chart by putting Month in Year to Axis and putting Year to Legend.

Regards,

[Paddhof1984]

@v-yuezhe-msft

I did append both tables into one table. The issue here now is, that my measures for the cumulative turnover for both years are refering to the respective single table. But in this case I'd need the cumulative turnover relying on the new table to2016+2017act for each year for every month. Any Idea how to implement this?

[v-yuezhe-msft · Accepted solution]

@Paddhof1984,

Create relationship between the new table and your calendar table using Date field. Then create YTD using the similar formula below and create Line chart as follows.

YTD = TOTALYTD(SUM(NewTable[field]),'Calendar'[Date])

Regards,

[Paddhof1984]

@v-yuezhe-msft

is there any append query in power bi I can use on this?

[v-yuezhe-msft]

@Paddhof1984,

You can follow Eric's suggestion in this similar thread to append columns of your tables.

Regards,

[v-yuezhe-msft]

@Paddhof1984,

You should add Year field to your visuals as shown in the following screenshot. Without the Year field, Power BI Desktop can't determine which YTD to show.  You can review the modified PBIX file to get details.



Another option is to append the RE2016 and RE2017act tables, then create YTD using TOTALYTD() function and create a line chart as shown in the screenshot below.

For more details about calculating YTD of different years, please review the following blog.
https://javierguillen.wordpress.com/2012/11/28/running-total-techniques-in-dax/

Regards,

[Paddhof1984]

Hello @v-yuezhe-msft

I added the year from calendar as filter, but now the cumulated turnover is shown as dots instead of lines. Any suggestion?

[v-yuezhe-msft]

@Paddhof1984 ,

Please review the PBIX file I send you. I create a YearMonth column in calendar table, and drag it to the Line chart.

Regards,

[Paddhof1984]

@v-yuezhe-msft

thanks for your support. In this case this isn't what I need. What I need is the cumulated turnover to be shown and to be compared to turnover from other years. This new line chart, building up on the new YearMonth column does not show the cumulated turnover.

My last post shows what I need, but instead of dots I do need lines to be shown.

[v-yuezhe-msft]

@Paddhof1984,

Click the third button to drill down the line chart. You can review the modified PBIX file to get more details.




Regards,

[Datatouille]

Hi,

This seems a bit strange. Will you please share your DAX measures, or, even better, your pbix file ?

I notice that you have a visual filter on your line chart ("DACH"). It might come from this ?

[Paddhof1984]

@Datatouille

Hello,

thanks for the hint. Right after I read your post I thought this might be the issue with the 2016 to cumulated not to be shown on the line chart. But I have removed the visual filter "DACH", but still there's only shown the turnover for 2017.

I do have two DAX-Measures:

TO2016 cumul. = TOTALYTD(SUM(RE2016[TO2016])|'Calendar'[Date])

TO2017 cumul. = TOTALYTD(SUM(RE2017act[TO2017])|'Calendar'[Date])

In case this souldn't help you in any ways, I'll share my pbix file with you. Thanks a lot.

[Paddhof1984]

Furthermore in case I am adding an additional Filter for year, if i choose 2016 and 2017 on the chart, the cumulated turnover for 2016 won't show up, only to for 2017, but if I deselect 2017 filter, 2016 is shown.

Any idea what this could be?

[v-yuezhe-msft]

@Paddhof1984,

Please share the PBIX file for us to analyze.  You can upload PBIX file to OneDrive and post shared link here.

Regards,

[Paddhof1984]

https://1drv.ms/u/s!AgBF1Ha5yWKVj4g3h0JNACQUvGQ0jw