Variation between two years in a chart

[PwrBI01]

Hi guys,

I would like to get the variation between the YTD Sales of the last year of 'Calendar table' and the YTD Sales of the previous year of that 'Calendar table'.

The problem I have is the following one:

1. I have created the measure:

'YTD Sales' = TOTALYTD(SUM(Sales[Sales]);'Calendar table'[Date])

2. I have introduced 'YTD Sales' and Calendar table [Year]' measures in a chart, getting the chart on the left side of the photo:

3. After that, I introduced a filter to show the last 2 years of 'Calendar table [Year]' (I mean the maximum year of 'Calendar table [Year]' and the previous one). This new chart is on the right side of the photo.

4. I would like to get the variation between the last year and the previous one as:

Variation between the last year and the previous one = ('YTD Sales current year'-'YTD Sales previous year')/'YTD Sales previous year'

Is possible to do it? I have tried multiple ways and I couldn't find a way that works.

Thanks in advance.

Regards.

[lbendlin]

Please provide sample data in usable format (not as a picture) and show the expected outcome.

[PwrBI01]

Hi @lbendlin,

Thank you for your answer.

The file is in the following link: https://www.dropbox.com/s/ptm2ltd8xg0bxqe/Problem%20Variation%20between%20years.pbix?dl=0

The chart has data until may 2021 included, and I have put a filter that shows the last 2 years with data in the chart, so if the month is from january to may it will show data from 2020 and 2021 and if the selected month is from june to december it will show data from 2019 and 2020, as you can see in the following photo:

As you know, I would like to compare the variation between the Total Year-To-Date sales from the years that appears in the chart, but I just get the sales from the selected month (not the Total Year-To-Date sales), as you can see.

Thanks in advance.

Regards.

[lbendlin]

You're making it too hard on yourself.

Var EndDateCurrentYear = EOMONTH(MAX('Calendar table'[Date]);0)   - I think you want to use Sales date, and not EOMONTH.

Var CurrentYTDSales = CALCULATE(TOTALYTD([YTD Sales];'Calendar table'[Date]);FILTER(Sales;Sales[Date]<=EndDateCurrentYear))  -  [YTD Sales] is already a measure, no need to do a TOTALYTD on it again.

I would recommend you go with the simple steps that I outlined.

[PwrBI01]

Hi @lbendlin,

Thank you for your answer.

On the one hand, I prefer to take EOMONTH because february of this year has 28 days, but the last one had 29 days, and I wanted to compare the whole month.

On the other hand, I made easier the measure following your tips:

VariationLast2Years2 =

Var EndDateCurrentYear = EOMONTH(MAX('Calendar table'[Date]);0)

Var CurrentYTDSales = CALCULATE([YTD Sales];FILTER(Sales;Sales[Date]<=EndDateCurrentYear))

Var EndDatePreviousYear = EOMONTH(MAX('Calendar table'[Date]);-12)

Var PreviousYTDSales = CALCULATE([YTD Sales];FILTER(Sales;Sales[Date]<=EndDatePreviousYear))

Return

(CurrentYTDSales-PreviousYTDSales)/PreviousYTDSales

But I continue having the problem that it calculates the sum of the month instead of calculate Year-To-Date sales.

Regards.

[lbendlin]

fair enough. In that case you can create a measure that

- looks at the max selected date for the current year

- collects all the sales for the current year with the max selected date a upper boundary (no need to compute the lower boundary if you have a regular calendar year)

- subtracts one year from the max selected date

- repeats the process for the same interval last year.

Alternatively use a table visual that will instantly give you a visual answer to the question without the need for any filtering. (Again assuming that your calendar table has a year column)

[PwrBI01]

Hi @lbendlin,

I am really sorry, I thought I had answered and I have just seen that there is no reply, so I think it didn't save.

I tried your way with this two measures:

YTD Sales = TOTALYTD(SUM(Sales[Sales]);'Calendar table'[Date])

VariationLast2Years2 =

Var EndDateCurrentYear = EOMONTH(MAX('Calendar table'[Date]);0)

Var CurrentYTDSales = CALCULATE(TOTALYTD([YTD Sales];'Calendar table'[Date]);FILTER(Sales;Sales[Date]<=EndDateCurrentYear))

Var EndDatePreviousYear = EOMONTH(MAX('Calendar table'[Date]);-12)

Var PreviousYTDSales = CALCULATE(TOTALYTD([YTD Sales];'Calendar table'[Date]);FILTER(Sales;Sales[Date]<=EndDatePreviousYear))

Return

(CurrentYTDSales-PreviousYTDSales)/PreviousYTDSales

It calculates correctly the max selected date for the current year and the previous one, but there is a problem with the Year-To-Date.

If I select the month April for example, it calculates the sales for April 2021 and the sales for April 2020 and calculate the difference between them, but it doesn't calculate the sales from January 2021 to April 2021 and from January 2020 to April 2020.

How could I change that?

Thanks in advance.

Regards.

[lbendlin]

Usually measures are for situations where the end user's actions (changing filters etc) impacts the outcome.  This is not such a situation (*) so a calculated column (which produces an immutable value) is more appropriate.

* small caveat - this approach assumes frequent (daily) dataset refreshes.

[PwrBI01]

Hi @lbendlin,

But the chart has a dynamic filter. According to the selected month the YTD is different, for example, if you select the month May the YTD is from January to May, while if you select December the YTD is from January to December.

[lbendlin]

You need to supply the filter for what "to date" means for prior years.  It may be as straightforward as adding a calculated column into your calendar table that says "is older than today minus one year"  or it may be more complex when your last transaction date of the current year is not today.

Warning:  Weekday patterns shift between years.  So even if you say "give me the transactions from last year until the same day as today"  you will still be comparing apples to pears. (Not oranges, but also not quite apples).

[PwrBI01]

Hi @lbendlin, thank you for your answer.

Is it possible to do it with measures instead of a new column on the calendar table?

I have created the following measure, but it doesn't work:

Variation between the last year and the previous one = 
Var EndDateCurrentYear = EOMONTH(MAX('Calendar table'[Date]);0)
Var StartDateCurrentYear = DATE(YEAR(EndDateCurrentYear);1;1)
Var EndDatePreviousYear = EOMONTH(MAX('Calendar table'[Date]);-12)
Var StartDatePreviousYear = DATE(YEAR(EndDatePreviousYear);1;1)
Var MeasureYTDSales = TOTALYTD(SUM(Sales[Sales]);'Calendar table'[Date])
Var CurrentYTDSales = CALCULATE(TOTALYTD(MeasureYTDSales;'Calendar table'[Date]);FILTER(Sales;Sales[Date]>=StartDateCurrentYear && Sales[Date]<=EndDateCurrentYear))
Var PreviousYTDSales = CALCULATE(TOTALYTD(MeasureYTDSales;'Calendar table'[Date]);FILTER(Sales;Sales[Date]>=StartDatePreviousYear && Sales[Date]<=EndDatePreviousYear))
Var VariationLast2Years = ROUND((CurrentYTDSales-PreviousYTDSales)/PreviousYTDSales*100;0)
Return
VariationLast2Years

Thanks in advance.

Regards.