Forum Discussion
Helper IOne date slicer, two table visuals showing different years
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!
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.
5 Replies
- MFelix
Super User
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/
- 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
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.
