Here is a little context.  I have product subcategory as row label.  I have a daily snapshot of inventory and using count of inventory as the measure.  The measure I want to be sure that I return the latest dated inventory when I don't specify a date as the column label.  But if a date is specified then I want to be sure to show that dates inventory in which the cell relates.  Inventory from day to day can go to 0.  So not everyday a subcategory has inventory. I have tried using LastNonEmpty to return the last date.  But what happens is one subcategory is from today and the next subcategory is from the previous day that did have inventory for that date.  I have been able to create a measure that returns the max date for which the column pertains.  Here is that formula.  CALCULATE(MAX([Business Date]), ALLEXCEPT('Inventory', 'Date')) 

What I cannot seem to figure out is how to put that into the filter context for the inventory count so I return inventory which only pertains to that date.  Any and all help is appreciated!

• Edited by DataNerd Tuesday, February 04, 2014 3:22 PM edit title

Hi DataNerd,

Thank you for your question. 

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

Regards,

We need to study your project in order to understand your business logic and help you with your enquiries.

From a support perspective this is really beyond what we can do here in the forums. If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone. Thanks.

If every subcategory can have a different date, you should use this technique:

Inventory :=
SUMX (
    VALUES ( Product[Subcategory] ),
    CALCULATE (
        SUM ( Inventory[Quantity] ),
        LASTNONBLANK (
            'Date'[Date],
            CALCULATE ( COUNTROWS ( Inventory ) )
        )
    )
)

You might also use this approach (but I prefer the former because it is potentially faster in the LASTNONBLANK iteration):

InventoryInternal := SUM ( Inventory[Quantity] )
Inventory :=
SUMX (
    VALUES ( Product[Subcategory] ),
    CALCULATE (
        [InventoryInternal],
        LASTNONBLANK (
            'Date'[Date],
            [InventoryInternal]
        )
    )
)

Hi Marco,

Thought this would be the solution for the same last non empty question I asked you on your site, but it still doesn't work.  Are you sure that your example gets the LAST value for each Product subcategory and not just the LAST value for the Product subcategories for the date context of the pivot?

For example, when I use your approach to aggregate the last value for each account as per the following DAX, I get the same result as SUM(EndingRemainingPrincBalance) at the day level. And not sure what is happening at the higher levels, i.e. week, month, quarter, year, but it looks like it's getting all of the last date amounts for that period ONLY if the account had a transaction in that period.  I need the last transaction prior to the context period, not just limited to that period.

Russo:=SUMX (
    VALUES ( DimAccount[CPAccountID] ),
    CALCULATE (
        SUM ( vwCPAccountRevenueRecognition[EndingRemainingPrincBalance] ),
        LASTNONBLANK (
            DimDate[FullDate],
            CALCULATE ( COUNTROWS ( vwCPAccountRevenueRecognition ) )
        )
    )
)

Hello again,

To illustrate my point further, I modified your DAX to count the unique accounts, aka products, used to aggregate the last inventory value.  It demonstrates that it's only using the accounts that have records on the pivot date context, NOT all accounts that have transactions prior to that date.  Hence, the total inventory value excludes accounts that don't have values in the context period.

=SUMX (
    VALUES ( DimAccount[CPAccountID] ),
    CALCULATE (
       DistinctCount ( vwCPAccountRevenueRecognition[cpaccountid] ),
        LASTNONBLANK (
            DimDate[FullDate],
            CALCULATE ( COUNTROWS ( vwCPAccountRevenueRecognition ) )
        )
    )
)

You're right, if you want to consider previous dates you have to extend the range of dates considered by LASTNONBLANK using the following syntax (see parts I added in bold):

Russo :=
SUMX (
    VALUES ( DimAccount[CPAccountID] ),
    CALCULATE (
        SUM ( vwCPAccountRevenueRecognition[EndingRemainingPrincBalance] ),
        CALCULATETABLE (
            LASTNONBLANK (
                DimDate[FullDate],
                CALCULATE ( COUNTROWS ( vwCPAccountRevenueRecognition ) )
            ),
            DATESBETWEEN (
                DimDate[FullDate],
                BLANK (),
                MAX ( DimDate[FullDate] )
            )
        )
    )
)