Playing with SAMEPERIODLASTYEAR and TOTALYTD functions

Let´s play with time intelligence DAX functions.

There is a table with two columns – timestamp and some numerical value.

At the beginning we will simply use the matrix visual and show the values in time. 

Year ago

And now? Maybe it would be nice to see the value year ago. That means we need this measure:

  • Year before = CALCULATE(SUM(my_values[Values]);SAMEPERIODLASTYEAR(my_values[Date]);ALL(my_values))

So for each period we see the value year ago.

Difference

So there are some values for this and for previous year. But what´s the difference in percents? It is the division only.

  • Year to year % difference = DIVIDE(SUM(my_values[Values])-[Year before];[Year before])

From the beginning of the year

It is also very easy.
  • Year to date = TOTALYTD(sum(my_values[Values]);my_values[Date];ALL(my_values))

Compare the beginning of this year with the beginning of last year

Do you want to know, if the YTD values for this year are better or worse compared to last year? Then simply combine both above mentioned values:

  • Year to date last year = CALCULATE([Year to date];SAMEPERIODLASTYEAR(my_values[Date]);ALL(my_values))
And the difference of this and last year YTD values is just very simple substraction: 
  • YTD Year to year difference = [Year to date]-[Year to date last year]
Nice, isn’t it?

2 Comments

  2. This will not work, if you have to work with several years :

    TOTALYTD(SUM(my_values[Values]), my_values[Date], ALL(my_values))

    it gave me the sum of the past year, since there were no values for this year.

    Instead I had to use the formula this way:
    TOTALYTD(SUM(my_values[Values]), my_values[Date], YEAR(my_values[Date]) = YEAR(TODAY()) )

    only with this I get a blank if there’s no value for this year available.

Comments are closed.