Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filtering a specific date and getting the sales amount

    Thanks, and I'm glad it helped
    Last edited by Matt Allington; Jan 30th, 2017 at 05:06 AM.
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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