Using a virtual table to filter another table in SUMX function

thank you for your quick reply! I really appreciate this.

I understand your concern, These are my real data sets:

"

One question I have for you to clarify is that I'm assuming that if there is a record in Stocks for a given date and product, that means it's inventory on hand, and if not, it's inventory out of stock.  Is that true?  Reason I'm asking is that if you compare my output of 11/15 and 11/16 I get different results than your expected results posted above...

"

The reason of the difference is I assume if there is no records on the date on the Stocks table then I take the nearest value. So, for 11 / 15 we have a not zero stocks for ProductID = 1 and the next record is on 11 / 20 with zero in stock. You assume that if we have no record on the date it means zero.

To get nearest stock value on the date I use this form

var TabOutOfStock = 
SUMMARIZE(
    Stocks,
    Stocks[ProductId],
    "Stock_QTY",
    MAXX(
        Filter(Stocks,
        Stocks[Date] = MAXX(
                        FILTER(
                            Stocks,
                            Stocks[Date] <= max('Date'[Date])
                            ),
                        [Date]
                        )
                ), 
        Stocks[Stocks])
    )

and that is why I didn't join the Stock.Date column with the Date table.

Here are the important measures...

Revenue In Stock Only = 
    VAR InventoryBalanceByDate =
        ADDCOLUMNS(
            ADDCOLUMNS(
                CROSSJOIN(
                    VALUES(Products[ProductId]),
			        VALUES('Calendar'[Date])
                ),
                "InventoryOnHand",
                [Inventory On Hand],
                "LastDateWithInventory",
                LASTNONBLANK(
                    FILTER(
                        ALL('Calendar'[Date]),
                        'Calendar'[Date] <= EARLIER('Calendar'[Date])
                    ),
                    [Inventory On Hand]
                )
            ),
            "AdjustedInventoryBalance",
            CALCULATE(
                [Inventory On Hand],
                FILTER(
                    ALL('Calendar'),
                    'Calendar'[Date] = [LastDateWithInventory]
                )
            )
        )
    RETURN
				
    IF(
        ISFILTERED('Calendar'[Date]),
        CALCULATE(
            [Total Revenue],
            FILTER(
                InventoryBalanceByDate,
                'Calendar'[Date] = MAX('Calendar'[Date]) &&
                [AdjustedInventoryBalance] <> 0
            )
        ),
        CALCULATE(
            [Total Revenue],
            FILTER(
                InventoryBalanceByDate,
                [AdjustedInventoryBalance] <> 0
            )
        )
    )

Revenue Out of Stock Only = 
    VAR InventoryBalanceByDate =
        ADDCOLUMNS(
            ADDCOLUMNS(
                CROSSJOIN(
                    VALUES(Products[ProductId]),
			        VALUES('Calendar'[Date])
                ),
                "InventoryOnHand",
                [Inventory On Hand],
                "LastDateWithInventory",
                LASTNONBLANK(
                    FILTER(
                        ALL('Calendar'[Date]),
                        'Calendar'[Date] <= EARLIER('Calendar'[Date])
                    ),
                    [Inventory On Hand]
                )
            ),
            "AdjustedInventoryBalance",
            CALCULATE(
                [Inventory On Hand],
                FILTER(
                    ALL('Calendar'),
                    'Calendar'[Date] = [LastDateWithInventory]
                )
            )
        )
    RETURN
				
    IF(
        ISFILTERED('Calendar'[Date]),
        CALCULATE(
            [Total Revenue],
            FILTER(
                InventoryBalanceByDate,
                'Calendar'[Date] = MAX('Calendar'[Date]) &&
                OR(
                    [AdjustedInventoryBalance] = 0,
                    ISBLANK([AdjustedInventoryBalance])
                )
            )
        ),
        CALCULATE(
            [Total Revenue],
            FILTER(
                InventoryBalanceByDate,
                OR(
                    [AdjustedInventoryBalance] = 0,
                    ISBLANK([AdjustedInventoryBalance])
                )
            )
        )
    )

What we're doing is building a table variable that represents all dates and products, the original inventory balance for that date/product, the last date that date/product had inventory, and then creating an "adjusted balance" equal to the last time there was a record for inventory for that date/product.  That ends up looking like this.

From there we're calculating total revenue and filtering for where that adjusted balance <> 0 (for in stock items) or either is blank or equal to zero (out of stock).

• For report details, also filtered for that specific date
• For report total, not filtered for dates

Hi, thanks a lot, really appreciate your help.

But it seams it doesn't work. Your Grand Total Revenue In/Out of stock are the same for each day however it coudn't be correcnt due to we have different items in stock on different days.

Please see examples

Hi littlemojopuppy ,

Thank you for your reply,

It seams it doesn't work.

At least [ProductId] should be TabStock[ProductId]

and what is the [Total Sales] in your CALCULATE clause? Is it a measure that sum sales up?

Slava

If you can share a sample pbix I'd be happy to do this for you...