YTD

[Paddhof1984]

Hello,

I am curious whether there is anyone who can help me out with following situation:

I do have 3 tables, each of them contains the total turnover from a specific year:

2015 (whole year)

2016 (whole year)

2017 (turnover ytd).

What I need or should I better say want to be shown on the chart table:

I need the current turnover from 2017 (ytd turnover) shown and compared to the turnover from past years (2015 and 2016) for the same timeperiod as the current year.

This means: f. e. today is 06/28/2017. So What I want to see is:

turnover 2017 from 01/17 - 06/17 (January - June)

turnover 2016 from 01/17 - 06/17 (January - June)

turnover 2015 from 01/17 - 06/17 (January - June)

I already tried to compare these 3 figures with first creating a total sum measure for each single year, but then I get stuck in how to proceed with creating further measures to get shown the ytd-turnover for each year. Any suggestions?

[Daniil]

Any chance you could supply some sample data so that it is easier for us to help you?

[Paddhof1984]

@Daniil

What I can provide you with the overview of the tables and their relationsships. Or which type of sample data do you exactly need?

I have the tables:

RE2015 (turnover for 2015)

RE2016 (turnover for 2016)

RE2017act (turnover for 2017 for the current year).

For 2015 and 2016 (always the past years) the same turnover periods should be shown as far as the turnover period is actually shown for the current year.

[Daniil]

Thanks @Paddhof1984. I would try something like this:

2015 YTD =
CALCULATE (
    SUM ( RE2015[QTY] ),
    INTERSECT ( VALUES ( RE2015[Month] ), VALUES ( RE2017act[Month] ) )
)

Does this help?

[Paddhof1984]

@Daniil

Thanks for your quick reply. No, somehow the measure shows empty on the chart:

[Daniil]

@Paddhof1984
Made a typo, sorry. This should work:

2015 YTD =
CALCULATE (
    SUM ( RE2015[QTY] ),
    INTERSECT ( VALUES ( RE2015[Month] ), VALUES ( RE2017act[Month] ) )
)

[Paddhof1984]

@Daniil

I have another point regarding the intersection of values: the Intersection of old years compared to the new year works perfect.

Now I do have following measure:

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

How can I intersect this one, so that only months are shown, where turnover is generated? In this case, it still shows values for the rest of the year and for months, which still are to come but where no turnover has been created until yet.

[Paddhof1984]

@Daniil Thanks for your support. One more thing:

Is there any possibility to add a measure which cumulates the monthly turnover for the ytd measure?

So I can compare ytd turnover for 2016 to the current to 2017?

[Daniil]

For this, I would append all three RE tables into one and have a proper date type column and a calendar table -- then the easiest way to create such a measure would be using Quick Measures.

[Paddhof1984]

@Daniil

Hello again.

Is there any possibility to limit the ytd for the same time period as last year only to previous months? Like July is the current month, but I only need the turnover for past months which are completed. IS there any possibility to add this to your measure above?

[Daniil]

Not in an easy way, as far as I know. It would be better to add a date column -- then you can definitely do what you want.