Using a virtual table to filter another table in SUMX function

hello dear littlemojopuppy ,

Thank you very much for your helping and efforts! I really appreciate this!

This formula doesn't work because it displayes the same value on each day.

However, I was able to workout with subquery and it works as expected now.

So, how it works?

I need to summarize all sales for all periods but only for Products that are in stock on a specific day

Grand total revenue in stock only = 

Calculate(
SUMX(
  Filter(
  All(Sales),
  Sales[ProductId] in <PRODUCTS IN STOCK ON THIS DAY>
  )
 )
)

"PRODUCTS IN STOCK ON THIS DAY" - is a one column table or a subquery.

To create this subquery I use SUMMARIZE function and it works good:

  var TabStock =
      SUMMARIZE(
            Stocks,
            Stocks[ProductId],
            "Stock_QTY",
            MAXX(
                Filter(Stocks,
                Stocks[Date] = MAXX(
                                FILTER(
                                    Stocks,
                                    Stocks[Date] <= d
                                    ),
                                Stocks[Date]
                                )
                        ), 
                Stocks[Stocks])
            )

 The problem is SUMMARIZE returns a table with two columns (ProductID and Stock_QTY) and that is why the table cannot be used in "IN" clouse. 

To improve this I have to make a one column table from the two columns table. To do this I use SELECTCOLUMNS function:

var d = MAX('Date'[Date])

var TabStock = 
SELECTCOLUMNS(
    FILTER(
        //tab creation section begin
        SUMMARIZE(
            Stocks,
            Stocks[ProductId],
            "Stock_QTY",
            MAXX(
                Filter(Stocks,
                Stocks[Date] = MAXX(
                                FILTER(
                                    Stocks,
                                    Stocks[Date] <= d
                                    ),
                                Stocks[Date]
                                )
                        ), 
                Stocks[Stocks])
            ),
            //tab creation section END
        [Stock_QTY] > 0
        ),
    "ProductId",
    [ProductId]
)

Now my measure with the subquery should work:

Thanks a lot for your assistens and time!

Best regards,

Slava.