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:
<tbody>
</tbody>
Calendar table:
<tbody>
</tbody>
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
<tbody>
</tbody>
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.
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 |
<tbody>
</tbody>
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 |
<tbody>
</tbody>
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 |
<tbody>
</tbody>
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.