locked
Get sales Amount value from one table base on max date RRS feed

  • Question

  • Dear All,

    I'm new to Power Pivot reporting tool, we have 2 tables one is Master table and Transaction table both the tables are related with customerId column.In table1 Data for customerID 44 having 2 records with different dates.

    Sample Data:

    Table 1:

    CustomerID Level Name InvoiceDate Total Base
      ComplianceFee2012   2
    4 ComplianceFee2012   5
    44 Management 1/31/2013 500
    44 Management 3/14/2014 500
    65 Corporate 2/15/2013 100
    65 Corporate 3/24/2014 100

    Table 2(Expected output):

    CustomerID   ManagementSales         CorporateSales

    44                  500 0

    65                   0 100

    I written below DAX query to populate data into ManagementSales and CorporateSales.

    Dax Query:

    =CALCULATE(SUMX('IR','IR'[SA]), FILTER
    ('IR',SEARCH("*Manage*",'IR'[Level Name],1,0)>
    0),Filter('IR','Sales Data'[Customer ID]='IR'[CustomerID]&&  'IR'[InvoiceDate]=Max('IR'[InvoiceDate])))

    When I use above it is not picking max invoice date from the table,because of this I'm getting Management sales as 1000 for Customer ID 44.

    Can you please help on how to fetch max invoice date from the table.

    Regards,

    Praveen


    Regards, Praveen

    • Moved by Elvis Long Friday, April 11, 2014 2:03 AM better for support
    Wednesday, April 2, 2014 5:11 PM

Answers

  • Based on your example-table this calculated measure should work:

    MyLastValue:=CALCULATE(
    SUM('Table 1'[Total Base]),
    LASTDATE('Table 1'[InvoiceDate])
    )

    further creating a pivot table with CustomerID on rows and [MyLastValue] as value should give you the described result

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Wednesday, April 23, 2014 7:53 AM
    • Marked as answer by Michael Amadi Thursday, May 8, 2014 11:04 AM
    Tuesday, April 22, 2014 12:33 PM
    Answerer

All replies

  • Praveen,

    Any luck with this?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, April 21, 2014 5:07 AM
  • Based on your example-table this calculated measure should work:

    MyLastValue:=CALCULATE(
    SUM('Table 1'[Total Base]),
    LASTDATE('Table 1'[InvoiceDate])
    )

    further creating a pivot table with CustomerID on rows and [MyLastValue] as value should give you the described result

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Wednesday, April 23, 2014 7:53 AM
    • Marked as answer by Michael Amadi Thursday, May 8, 2014 11:04 AM
    Tuesday, April 22, 2014 12:33 PM
    Answerer