# Thread: Filtering a specific date and getting the sales amount Thanks: 0 Likes: 0

1. ## Filtering a specific date and getting the sales amount

Hi all;
I am working with a power pivot report with two tables (SalesFact and Calendar) and need to get the amount sold of an item in the corresponding date a year ago. The two tables look like this:

SalesFact table:
 Item SaleDate SoldAmount Banana 01/02/2014 \$3.00 Pear 01/05/2014 \$10.00 Banana 01/01/2015 \$5.00 Pear 01/04/2015 \$4.00

Calendar table:
 Year Date MatchingDateLastYear 2015 01/01/2015 01/02/2014 2015 01/02/2015 01/03/2014 2015 01/03/2015 01/04/2014 2015 01/04/2015 01/05/2014

The tables are connected by the columns SaleDate (SalesFact table) and Date (calendar table).

I need to create a pivot table with a calculated field to get the "SoldAmount" value of an item in the "MatchingDateLastYear". The name of this calculated field is "MatchingDateLastYearSoldAmount", below is an example of the pivot table with this calculated field results:

Year filtered in the pivot table: 2015

 Item SaleDate SoldAmount MatchingDateLastYearSoldAmount Banana 01/01/2015 \$5.00 \$3.00 Pear 01/04/2015 \$4.00 \$10.00

Here is the explanation of the results under "MatchingDateLastYearSoldAmount" above:

By filtering the year 2015 the pivot table displays the following:

1) "Banana", sold on 01/01/2015, amount of \$5.00. The "MatchingDateLastYear" for 01/01/2015 in the calendar table is 01/02/2014 and on that date the banana "SoldAmount" is \$3.00 in the SalesFact table.

2) "Pear", sold on 01/04/2015, amount of \$4.00. The "MatchingDateLastYear" for 01/04/2015 in the calendar table is 01/05/2014 and on that date the pear "SoldAmount" is \$10.00 in the SalesFact table.

I am having difficulties figuring out how to create the calculated field "MatchingDateLastYearSoldAmount". Any help will be appreciated.

Thanks.

2. ## Re: Filtering a specific date and getting the sales amount

You need time intelligence. Read my article here and post back if you need any more help
DAX Time Intelligence Explained - Excelerator BI

3. ## Re: Filtering a specific date and getting the sales amount

Matt, this article was an excellent guide to the solution, it was very well explained.
I am using a variation of the "CALCULATE([Total Sales],DATESADD(Calendar[Dates],-1,YEAR))".
Thank you very much.

4. ## Re: Filtering a specific date and getting the sales amount

Thanks, and I'm glad it helped

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•