Using a virtual table to filter another table in SUMX function

[SlavaSha7]

Hello dears,

I have a sales table with fields:

* Date

* ProductID

* Revenue

and a Stocks table with fields:

* Stock Date

* ProductID

* InStock_Quantity

I need to calculate year revenue of products that in stock by day, for example,

Date	Revenue In Stock	Revenue Out of Stock
12/01/2020	0.7M	0.3M
12/02/2020	0.69M	0.31M
12/03/2020	0.8M	0.2M

i.e. the total revenue of 2020 year is 1M, on 1st Dec we had some products that made 0.7M in our yearly revenue. We had another set of products on the 2th Dec that made 0.69M in our yearly revenue.

I have created a virtual table to get stocks by products by day

var TabStock = 
SUMMARIZE(
    Stocks,
    Stocks[ProductId],
    "Stock_QTY",
    MAXX(
        Filter(Stocks,
        Stocks[Stock Date] = MAXX(
                        FILTER(
                            Stocks,
                            Stocks[Stock Date] <= max('Date'[Date])
                            ),
                        [Stock Date]
                        )
                ), 
        Stocks[Max In Stock])
    )

Now I would like to filter the Sales table by products that are in stock using the above virtual  table, however I cannot write a right DAX expression for IN condition. Perhaps my issue is I must create a physical table and it's not possible to use a virtual table in the expression to filter anoter table.

So my result query is:

return
Calculate(
    SUMX(
        FILTER(Sales,
            Sales[ProductId] in VALUES(
                                                Filter(Allexcept(
                                                    TabStock, 
                                                    [ProductID]), 
                                                    TabStock[Stock_QTY] > 0
                                                )
                                                )
        ),
    Sales[Revenue]
    )
    )

I cannot work out with the right part of the IN section or perhaps I should use completely another approach.

If simplify, my measure should be like this:

Select
  Date,
  sum(revenue) as RevenueInStock
from Sales S
Where S.ProductID in
      (
        select distinct(S.ProductID) from Stocks S where S.Stock_Quantity > 0
      )

Thank you for advance.

Best regards,

Slava

[littlemojopuppy]

Hi @SlavaSha7

Ok...I finally got this.  You can download from here.

Here's the results...

[SlavaSha7]

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

[littlemojopuppy]

Can you explain how you're calculating In Stock and Out of Stock in that Excel workbook?

[SlavaSha7]

---

@littlemojopuppy wrote:

Can you explain how you're calculating In Stock and Out of Stock in that Excel workbook?

---

Grand Total Revenue In Stock Only = Grand Total Revenue filtered by products that are in stock on the date.

We take Grand Total Revenue as revenue for the year (or all to simplify) and exclude revenue of products that are out of stock on the date 

Particularly:

We have Grand Total Revenue (your measure)

 = $5870

Grand Total Revenue = 
    CALCULATE(
        [Total Revenue],
        ALL('Calendar')
    )

Then we have my formula that calculates quantity in stock by products for the date

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

According to our test data set we have:

02 Jan 2020 we have no products in stock at all 

Grand Total Revenue In Stock Only = $0

Grand Total Revenue Out of Stock Only = $5870 (grand total revenue)

The same on 3 Jan and 30 Jan

15 Nov 2020 we have in stock only Product ID = 1. Grand total revenue of ProductID=1 is $870

Grand Total Revenue In Stock Only = $870

Grand Total Revenue Out of Stock Only = $5000 (grand total revenue of product that are our of stock on 15 Nov).

16 Nov is the same

3 Dec 2020 we have no products in stock at all 

Grand Total Revenue In Stock Only = $0

Grand Total Revenue Out of Stock Only = $5870 (grand total revenue)

16 Dec 2020 we have in stock only Product ID = 2. Grand total revenue of ProductID=2 is $5000

Grand Total Revenue In Stock Only = $5000

Grand Total Revenue Out of Stock Only = $870 (grand total revenue of products that are our of stock on 16 Dec).

So what we need is:

Grand total revenue in stock only = 

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

Please let me know if I should clarify something

Thanks.

[littlemojopuppy]

@SlavaSha7 Yes...clarification.  To start with you don't have $870 revenue on 11/15 or 11/16.  You have $120 on each date.

If what you're looking for is total yearly revenue less in stock or out of stock on any given date that's simply going to be this

Grand Total Revenue = 
    CALCULATE(
        [Total Revenue],
        ALL('Calendar')
    )

Grand Total Revenue In Stock Only = [Grand Total Revenue] - [Revenue In Stock Only]

Grand Total Revenue Out of Stock Only = [Grand Total Revenue] - [Revenue Out of Stock Only]

[SlavaSha7]

Yes...clarification.  To start with you don't have $870 revenue on 11/15 or 11/16.  You have $120 on each date.

 We do not calculate revenue on a date, we only work with total revenue, so if Product 1 is out of stock we substract total revenue of Product 1 from Total revenue.

For Product =1 Total revenue is $870

If what you're looking for is total yearly revenue less in stock or out of stock on any given date that's simply going to be this

---

I am looking for total yearly revenue of items that are In of stock on any given date 

"PRODUCTS IN STOCK ON THIS DAY" on my example below should be like a sub query

Grand total revenue in stock only = 

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

and I got stock on building IN clouse and this sub query.

[littlemojopuppy]

From your original post...

So...yes, I thought you needed it by date because that was the original request.

Furthermore, looking at just Product 1 and the $870 of revenue, that's distributed between in stock and out of stock depending on what date you happen to be looking at.

So you are looking at it by date, even if the final result may not be presented by date.

If what you are looking for is Total In Stock/Out of Stock over the course of the year...

Grand Total Revenue In Stock Only = 
    CALCULATE(
        [Revenue In Stock Only],
        ALL('Calendar')
    )

Grand Total Revenue Out of Stock Only = 
    CALCULATE(
        [Revenue Out of Stock Only],
        ALL('Calendar')
    )

[SlavaSha7 · Accepted solution]

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.

[littlemojopuppy]

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

[littlemojopuppy]

Hope this helps!  🙂

[littlemojopuppy]

Sorry about asking if I was doing your homework...the word "lab" in the file name and the limited amount of data made me wonder.  Hope I didn't offend 🙂

Ok...download what I did from here.

First, I changed your data model to look like this.  Calendar/Date and Products are dimensions for both Sales and Stocks.

I also created a different date table using the CALENDARAUTO() function instead of what you created in Power Query...I didn't want to screw up importing any of the other data.

Some measures...

Total Revenue = SUM(Sales[Revenue])

Revenue In Stock Only = 
    VAR	InventoryOnHand =
        SUMMARIZE(
            'Calendar',
            'Calendar'[Date],
            "InventoryOnHand",
            [Inventory On Hand] + 0
        )
    RETURN
				
    CALCULATE(
	    [Total Revenue],
    	FILTER(
            InventoryOnHand,
            [InventoryOnHand] <> 0
        )
    )

Revenue Out of Stock Only = 
    VAR	InventoryOnHand =
        SUMMARIZE(
            'Calendar',
            'Calendar'[Date],
            "InventoryOnHand",
            [Inventory On Hand] + 0
        )
    RETURN
				
    CALCULATE(
	    [Total Revenue],
    	FILTER(
            InventoryOnHand,
            [InventoryOnHand] = 0
        )
    )

Grand Total Revenue = 
    CALCULATE(
        [Total Revenue],
        ALL('Date')
    )

Grand Total Revenue In Stock Only = 
    CALCULATE(
        [Revenue In Stock Only],
        ALL('Date')
    )

Grand Total Revenue Out of Stock Only = 
    CALCULATE(
        [Revenue Out of Stock Only],
        ALL('Date')
    )

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...

[SlavaSha7]

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.

[littlemojopuppy]

To make sure I understand this correctly, what you're saying is that in the absence of a value on for a given date/product, you're assuming that the most recent inventory balance is still valid?

Listen...my wife starts her last semester of grad school tonight and she's already taken over the office.  Can't answer tonight but will do so tomorrow.  Fair?

[SlavaSha7]

---

@littlemojopuppy wrote:

To make sure I understand this correctly, what you're saying is that in the absence of a value on for a given date/product, you're assuming that the most recent inventory balance is still valid?

That's correct

[littlemojopuppy]

By the way...those are really big PBI files.  Wondering if there might be another solution for them

[SlavaSha7]

---

@littlemojopuppy wrote:

By the way...those are really big PBI files.  Wondering if there might be another solution for them

---

Thank you very much for your help.

That is why I have created a test PBX file and test data to check new report

[littlemojopuppy]

Ok...thought I was doing something unethical 😉

[SlavaSha7]

Is it possible to implement FILTER to the VALUES function?
Like this:

var LookupTab = Values(Stocks[ProductId), Stocks[QTY_InStock] > 0)

[littlemojopuppy]

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

[SlavaSha7]

Hi @littlemojopuppy and happy New Year!

I will appreciate it!

I attached my lab PBX file and comments, we can have a call to clarify, my skype is shamakrus

PBX file 

Thanks for advance.

Slava