Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MaxItaly
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
BhaveshPatel
Community Champion
Community Champion

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
BhaveshPatel
Community Champion
Community Champion

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.