One date slicer, two table visuals showing different years

[LuukP]

Hello everyone,

Here is a (hopefully) simple problem that I've been struggling with on various occasions. I'll use a simple example to illustrate what I mean:

Suppose I have a table with the following columns:
Book Title | Author | Received Date

Using a relative Date Slicer (using a separate Dates table), I will have one table visual showing all books received in the last 3 months. No problem.

Now, I'd like to have a second table visual showing all books received in the same period last year.

How do I do that?

Thank you in advance!

[LuukP]

Hi Miguel,

Thanks for your response.  The issue is that there is no value that I am trying to calculate.  There is no SalesAmount or similar value, just a list of records that have a date field.  So, can I do this without using a CALCULATE function?

LuukP

[MFelix · Accepted solution]

Hi @LuukP ,

In this case you need to do for example a count rows and this depending on the columns you use will return for example 1 (if you selected a specific book), or 10 (if you aggregate by author or date) then you can use that value to filter your list.

I created the following measure:

Books previou year = 

CALCULATE (
   COUNTROWS(Books),
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)

See result below:

Has you can see I'm getting the books for current year and previous year.

If you need you can change the metric to give you a list of books or dates depending on the aggregation.

Making a small change I have  created one for the books and use it in a list:

Books previou year = 

CALCULATE (
   CONCATENATEX(Books,Books[Book Title], ","),
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)

You just need to pick up one value to get the filter made for you data.

[LuukP]

Thank you for the solution.  I should note that I had to set up a one-to-many relationship between my date table and my books table - then it worked.

[MFelix]

@LuukP ,

Forgot to mention that, I always assume that the relationship is active.

[MFelix]

Hi @LuukP ,

For this you need to create a measure that gets the values for the same period last year something similar to 

Previous Year =

CALCULATE (
    [Sales Amount],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

This is one of the options check the links below with the explanation of this calculations and some similar.

https://www.sqlbi.com/articles/compare-equivalent-periods-in-dax/

https://www.sqlbi.com/tv/dateadd-sameperiodlastyear-dax-guide/