locked
How to get max date in period RRS feed

  • Question

  • Hi,

      I have 2 table in Power Pivot. 

    Table 1 is a Purchase Invoice list table and has ItemCode, Posting Date, Unit Price, NoMY (ItemCode+Month+Year) Group by ItemCode, month, year.

    Table 2 is a Work Order component table and has ItemCode, Posting Date, quantity, NoMY(ItemCode+Month+Year).

    Table1 & Table2 has relationship by NoMY. For now, I want to get T1 Unit Price in table 2, so I used related() to get T1.UnitPrice in table2. but some T2.unitPrice is blank, because No purchase of item in that month . how to set a DAX to get the T1.UnitPrice if T1.NoMY <> T2.NoMY but get the last T1.NoMY ?

    For example, Item A1 in T1, the NoMY is 012015, 052015, 062015.. and T2.NoMY is 012015, 022015,032015,042015..

    How to get T1.NoMY 012015 Unit price when T2.NoMY are 022015, 032015,042015 ?

    Thanks


    James Liang

    Monday, November 23, 2015 11:20 PM

Answers

  • This sort of work is better suited to the ETL layer or your query from your data source if you don't have any influence on the ETL process. Power Query is a great tool with strong integration with Power Pivot which would be better to do this sort of work in. If you're querying a SQL data source, then this is much more simply expressed in SQL.

    That being said, if you have a strong requirement to do this in Power Pivot and use a DAX calculated column, this will do it for you:

    UnitPrice =
    CALCULATE(
    	VALUES( PurchaseInvoice[UnitPrice] )
    	,CALCULATETABLE(
    		TOPN(
    			1
    			,PurchaseInvoice
    			,PurchaseInvoice[PostingDate]
    			,DESC
    		)
    		,PurchaseInvoice[PostingDate] <= EARLIER( WorkOrder[PostingDate] )
    		,PurchaseInvoice[ItemCode] = EARLIER( WorkOrder[ItemCode] )
    	)
    )

    Here we use CALCULATE() which allows us to define a custom context in which to evaluate VALUES( PurchaseInvoice[UnitPrice] ). VALUES() returns a one-column table of distinct elements in the column referenced. If that column has one row, then you get a 1x1 table which implicitly converts to a scalar and can be evaluated for a calculated column.

    Now we need to guarantee that we only return a single value. We use CALCULATETABLE() to restrict the rows which will be considered from PurchaseInvoice for evaluating our VALUES(). We use TOPN() to return the number of rows specified (1), from the table specified (PurchaseInvoice), sorting by a specific expression (PurchaseInvoice[PostingDate]), in DESCending order.
    To make sure that our 1 row returned is the right one we apply two conditions in our CALCULATETABLE(), the first that PurchaseInvoice[PostingDate] must be less than or equal to EARLIER( WorkOrder[PostingDate] ). EARLIER() is a way to refer to the row context in which we are evaluating the entire calculated column definition. We also make sure that PurchaseInvoice[ItemCode] is equal to the current row in WorkOrder.

    By filtering PurchaseInvoice as such, the TOPN() is guaranteed to return either the PurchaseInvoice[UnitPrice] value from the same day as the current row in WorkOrder, or the most recent one, for the current item.

    Ninja edit: No relationship is necessary between the tables for this, and if you have an active relationship, you likely will get different results. You'd have to escape the context coming from that relationship with some ALL()s.

    GNet Group BI Consultant


    • Edited by greggyb Tuesday, November 24, 2015 12:06 AM
    • Proposed as answer by Darren GosbellMVP Tuesday, November 24, 2015 12:42 AM
    • Marked as answer by JamesLiang Tuesday, November 24, 2015 10:28 PM
    Tuesday, November 24, 2015 12:05 AM