topic Re: Using a virtual table to filter another table in SUMX function in DAX Commands and Tips

Using a virtual table to filter another table in SUMX function

SlavaSha7 — Mon, 28 Dec 2020 16:06:45 GMT

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

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Mon, 28 Dec 2020 17:04:00 GMT

Hi! Try this instead...

VAR InventoryInStock = FILTER( ALLEXCEPT( TabStock, [ProductID] ), TabStock[Stock_QTY] > 0 ) RETURN CALCULATE( [Total Sales], InventoryInStock )

Re: Using a virtual table to filter another table in SUMX function

SlavaSha7 — Tue, 29 Dec 2020 17:20:59 GMT

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

Re: Using a virtual table to filter another table in SUMX function

SlavaSha7 — Tue, 29 Dec 2020 17:58:10 GMT

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

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

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Tue, 29 Dec 2020 21:39:37 GMT

I wrote some DAX that if you adapt it to your data model, it should work.

[Total Sales] would be SUM([the revenue field]. And try TabStock[ProductId] in place of where I put [Product ID]

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Tue, 29 Dec 2020 21:40:39 GMT

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

Re: Using a virtual table to filter another table in SUMX function

SlavaSha7 — Sun, 03 Jan 2021 19:22:34 GMT

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

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Sun, 03 Jan 2021 20:36:22 GMT

Is this your homework??? 🤔

Re: Using a virtual table to filter another table in SUMX function

SlavaSha7 — Sun, 03 Jan 2021 20:45:45 GMT

littlemojopuppy no 🙂

I have a big PBX file with ~2M rows and to simplify my queries I have created a very simple file then I am going to copy formulas to my real file.

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Sun, 03 Jan 2021 20:46:43 GMT

Ok...thought I was doing something unethical 😉

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Sun, 03 Jan 2021 21:10:42 GMT

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

Re: Using a virtual table to filter another table in SUMX function

SlavaSha7 — Sun, 03 Jan 2021 22:02:33 GMT

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

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

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.

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Sun, 03 Jan 2021 22:17:02 GMT

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?

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Sun, 03 Jan 2021 22:19:15 GMT

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

Re: Using a virtual table to filter another table in SUMX function

SlavaSha7 — Mon, 04 Jan 2021 09:30:18 GMT

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

Re: Using a virtual table to filter another table in SUMX function

SlavaSha7 — Mon, 04 Jan 2021 09:41:42 GMT

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

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Mon, 04 Jan 2021 17:04:11 GMT

Hi SlavaSha7

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

Here's the results...

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Mon, 04 Jan 2021 17:15:00 GMT

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

Re: Using a virtual table to filter another table in SUMX function

littlemojopuppy — Mon, 04 Jan 2021 17:16:43 GMT

Hope this helps! 🙂

Re: Using a virtual table to filter another table in SUMX function

SlavaSha7 — Mon, 04 Jan 2021 20:12:30 GMT

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