Forum Discussion

SlavaSha7's avatar
SlavaSha7
Helper I
5 years ago
Solved

Using a virtual table to filter another table in SUMX function

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...
DAX
SUMMARIZE
Virtual Table
  • SlavaSha7's avatar
    SlavaSha7
    5 years ago

    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's avatar
littlemojopuppy
Community Champion
5 years ago

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's avatar
    littlemojopuppy
    Community Champion
    5 years ago

    Hope this helps!  ğŸ™‚