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

[MaxItaly]

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!

[BhaveshPatel · Accepted solution]

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

[MaxItaly]

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