How Can I Show Count Of 2 Column Values Into A Pivot Table With Grouped Values As Rows?-CodePudding

I have a data table in Excel simplified as below.

I am trying to make a Pivot Table as below, with Week Data expected to grow each week.

Current Pivot Table uses Data Models, so I believe I am limited to Measures and no Calculated Fields (correct me if I am wrong).

I have tried creating 2 Data Models and create a relationship based on Week, but it is not allowed since the values are not unique. Getting the Items Sold count might be the missing piece I need. I think Inventory column should be solved by creating a measure to find the difference between Items Added and Items Sold.

I have been pulling my hair out trying to solve this. I feel it should be possible, and I am just missing the obvious solution in front of me.

CodePudding user response：

Assuming:

Your source table is named Data
You have another table named Weeks which comprises a single column named Week and which contains a distinct list of all weeks to be found in the Data table, e.g. "WEEK01", "WEEK02", "WEEK03", "WEEK04", "WEEK05"

First create two relationships between the Data and Weeks tables, one to the Added Week column (which should be active), and one to the Sold Week column (which should be inactive).

Then create the following measures:

Items Added:=COUNTROWS( Data )

Items Sold:=0
      CALCULATE(
        COUNTROWS( Data ),
        USERELATIONSHIP ( Data[Sold Week], Weeks[Week] )
    )

Inventory:=VAR ThisWeek =
    MIN( Weeks[Week] )
VAR CumulativeAdded =
    CALCULATE( [Items Added], Weeks[Week] <= ThisWeek )
VAR CumulativeSold =
    CALCULATE(
        [Items Sold],
        FILTER(
            ALL( Weeks ),
            Weeks[Week] <= ThisWeek
                && NOT ( ISBLANK( Weeks[Week] ) )
        )
    )
RETURN
    CumulativeAdded - CumulativeSold

which can then be placed in a Pivot Table alongside the Week column from the Weeks table.

A (blank) row may be present in this table, which you can filter out if you wish.