cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Viewing in a matrix the sales by year when using a visual filter object

Hello everyone,

I am having some issues creating a report.

What I'd like to achieve is a report where I have a visual filter object where I can select the year of the sales, and a matrix where in the rows I see the countries, and in the first column I have the sum of the sales in the selected year (in this case, 2014) and in the second column I'd want to see the sales of the previous year selected (2013) in the visual object.

The only problem I am experiencing is the creation of the second column (the previous year sales) because I'm having conflicts with the dynamic selection of the visual filter object.

 

The dataset in this example may be described as:

Order  Country  Amount  Year

1         Spain     15           2014

2         Spain     180         2014

3         USA       12           2013

.....

 

Thanks to everyone who could give me a hint!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Viewing in a matrix the sales by year when using a visual filter object

Hi @MaxItaly

 

You should check out the below blog:

http://www.daxpatterns.com/time-patterns/

 

You can use something like this:

 Create a date table in your report first and

 

 Create a date column in your fact table using the DATE function.

 

 Create relationship in your data model with date being the common key in both tables.

 

Sales Prior Year := CALCULATE(SUM[Amount],SAMEPERIODLASTYEAR('Date'[DateKey]))

 

Thanks,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

2 REPLIES 2
Highlighted
Community Champion
Community Champion

Re: Viewing in a matrix the sales by year when using a visual filter object

Hi @MaxItaly

 

You should check out the below blog:

http://www.daxpatterns.com/time-patterns/

 

You can use something like this:

 Create a date table in your report first and

 

 Create a date column in your fact table using the DATE function.

 

 Create relationship in your data model with date being the common key in both tables.

 

Sales Prior Year := CALCULATE(SUM[Amount],SAMEPERIODLASTYEAR('Date'[DateKey]))

 

Thanks,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

Highlighted
Helper III
Helper III

Re: Viewing in a matrix the sales by year when using a visual filter object

Many thanks, I managed to overcome my issue! Many thanks!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors