How to create a Summary Table from other tables in model.

[Peter4InfoCen]

I hve been as Power BI Forum lurker for a long time and have found these forums super helpful. Now my first attempt to get some help - really frustrated trying to get the following working with not much success (and much time spent).

The following is a summary of the tables I need to work with:

I have an inventory count table (ITSCTrans) that carries the counted qty of specific items (EntryITItemLocation) and each counted item (EntryITSCTran) carries the count date and last time the item was counted. In order to enable further analysis of the count variances, I need to build a table that shows the total qty of orders received by customer (EntryARCustomer) for each counted item (EntryITSCTran) for the period from the last count date to the current count date ot the item. The SOOrderLineTrans (and related tables) identifies the orders that have been received by customer (EntryARCustomer).  

The summary table to be: ITSCTrans[EntryITSCTran],SOOrderLineTrans[EntryARCustomer],SUM(SOOrderLineTrans[ItemQty]).

Have tried numerous was to achieve this and the following is one of the options I have attempted. I had assumed that, with GENERATE, the second table would be able to "see" the values from the first table - but the second table does not recognise the fields EntryITItemLocation,[AutoClearDate] and [AutoClearDateLast] from the first table.

EVALUATE

   GENERATE(SELECTCOLUMNS(

                ITSCTrans,

                "EntryITSCTran",ITSCTrans[EntryITSCTran],

                "EntryITItemLocation",[EntryITItemLocation],

                "AutoClearDateLast",RELATED(ITSCExtracts[AutoClearDate_Last]),

                "AutoClearDate",RELATED(ITSCExtracts[AutoClearDate])),

            CALCULATETABLE(

                SUMMARIZE(

                    SOOrderLineTrans,

                    SOOrderLineTrans[EntryARCustomer],

                    "ItemQty",SUM(SOOrderLineTrans[ItemQty])),

                    ALL(SOOrderLineTrans),

                    SOOrderLineTrans[EntryITItemLocation] = [EntryITItemLocation] && SOOrderLineTrans[TranDate]>[AutoClearDateLast] && SOOrderLineTrans[TranDate]<=[AutoClearDate]))

Please advise how to generate the required summary table. Any assistance much appreciated.

[DataNinja777]

Hi @Peter4InfoCen ,

It seem the question focuses only on stock out (customer orders from the SOOrderLineTrans table). It calculates the total quantity ordered by customers (StockOutQty) between the last inventory count date and the current inventory count date.

It does not yet include stock-in data (purchases) or other factors that might influence inventory variance, such as returns or adjustments.

To comprehensively calculate the net variance between inventory counts, we should include both:

• Stock Out (customer orders): Using the SOOrderLineTrans table.
• Stock In (purchases): Using the PurchaseOrderLineTrans table or equivalent.

EVALUATE
VAR ITSCTransSummary =
    ADDCOLUMNS(
        ITSCTrans,
        "AutoClearDateLast", RELATED(ITSCExtracts[AutoClearDate_Last]),
        "AutoClearDate", RELATED(ITSCExtracts[AutoClearDate])
    )

VAR StockOutSummary =
    GENERATE(
        ITSCTransSummary,
        SUMMARIZE(
            FILTER(
                SOOrderLineTrans,
                SOOrderLineTrans[TranDate] > ITSCTransSummary[AutoClearDateLast] &&
                SOOrderLineTrans[TranDate] <= ITSCTransSummary[AutoClearDate] &&
                SOOrderLineTrans[EntryITItemLocation] = ITSCTransSummary[EntryITItemLocation]
            ),
            ITSCTrans[EntryITSCTran],
            SOOrderLineTrans[EntryARCustomer],
            "StockOutQty", SUM(SOOrderLineTrans[ItemQty])
        )
    )

VAR StockInSummary =
    GENERATE(
        ITSCTransSummary,
        SUMMARIZE(
            FILTER(
                PurchaseOrderLineTrans,
                PurchaseOrderLineTrans[TranDate] > ITSCTransSummary[AutoClearDateLast] &&
                PurchaseOrderLineTrans[TranDate] <= ITSCTransSummary[AutoClearDate] &&
                PurchaseOrderLineTrans[EntryITItemLocation] = ITSCTransSummary[EntryITItemLocation]
            ),
            ITSCTrans[EntryITSCTran],
            "StockInQty", SUM(PurchaseOrderLineTrans[ItemQty])
        )
    )

VAR CombinedSummary =
    ADDCOLUMNS(
        ITSCTransSummary,
        "StockOutQty", 
            SUMX(
                FILTER(
                    StockOutSummary,
                    StockOutSummary[EntryITSCTran] = ITSCTrans[EntryITSCTran]
                ),
                [StockOutQty]
            ),
        "StockInQty", 
            SUMX(
                FILTER(
                    StockInSummary,
                    StockInSummary[EntryITSCTran] = ITSCTrans[EntryITSCTran]
                ),
                [StockInQty]
            ),
        "NetVariance",
            ITSCTrans[QtyCounted] + [StockInQty] - [StockOutQty]
    )

RETURN
    SELECTCOLUMNS(
        CombinedSummary,
        "EntryITSCTran", ITSCTrans[EntryITSCTran],
        "EntryITItemLocation", ITSCTrans[EntryITItemLocation],
        "StockOutQty", [StockOutQty],
        "StockInQty", [StockInQty],
        "NetVariance", [NetVariance]
    )

If you want to incorporate both stock out and stock in, the DAX above is the recommended approach. Let me know if you’d like further clarification!

Best regards,

[Peter4InfoCen]

Thanks for your response.

I already have the total movement of counted stock for each of the counted items in a separate table (ie: Sales Qty, Receipts,Issues,Adjustemnts etc). What we are trying to do is to get the total qty of the item that was ordered over the period so that we can compare ordered qty vs dispatched qty (sales).

I modified your result to focus on just getting the OrderQty and still get the same type of error that I originally got - am running this in DAX Query View:

Query (15, 46) Cannot find table 'ITSCTransSummary

EVALUATE

VAR ITSCTransSummary =

    ADDCOLUMNS(

        ITSCTrans,

        "AutoClearDateLast", RELATED(ITSCExtracts[AutoClearDate_Last]),

        "AutoClearDate", RELATED(ITSCExtracts[AutoClearDate])

    )

VAR StockOutSummary =

    GENERATE(

        ITSCTransSummary,

        SUMMARIZE(

            FILTER(

                SOOrderLineTrans,

                SOOrderLineTrans[TranDate] > ITSCTransSummary[AutoClearDateLast] &&

                SOOrderLineTrans[TranDate] <= ITSCTransSummary[AutoClearDate] &&

                SOOrderLineTrans[EntryITItemLocation] = ITSCTransSummary[EntryITItemLocation]

            ),

            ITSCTrans[EntryITSCTran],

            SOOrderLineTrans[EntryARCustomer],

            "OrdersRecdQty", SUM(SOOrderLineTrans[ItemQty])

        )

    )

RETURN

   //ITSCTransSummary

    SELECTCOLUMNS(

        SttockOutSummary,

        "EntryITSCTran", ITSCTrans[EntryITSCTran],

        "EntryITItemLocation", ITSCTrans[EntryITItemLocation],

        "StockOutQty", [StockOutQty]

    )