"Rolling" sum of a subgroup using the most recent record of each item in the group at a given date?

[Crefin]

Let's say I have an inventory table of products.  Items are in categories such as "produce" and "meat" while also in subcategories like "fruit" and "vegetables".  Each record in the table is the inventory at a given date for each product.  Inventory is not done consistently where there is a record within the subcategory for each product each day.  They vary.  What is needed is the total inventory levels for each category and subcategory at the time of each transaction.

Here is an example table:

Item	Inventory	Date	Category	Subcategory	Category Total	Subcategory Total
1	0	1/1/2021	Produce	Fruit
2	0	1/1/2021	Produce	Vegetables
3	0	1/1/2021	Produce	Vegetables
2	50	1/2/2021	Produce	Vegetables
2	48	1/5/2021	Produce	Vegetables
1	10	1/7/2021	Produce	Fruit
1	8	1/8/2021	Produce	Fruit
2	42	1/8/2021	Produce	Vegetables
1	4	1/10/2021	Produce	Fruit
3	16	1/12/2021	Produce	Vegetables
5	5	1/18/2021	Meat	Deli
3	11	1/31/2021	Produce	Vegetables
4	14	2/1/2021	Meat	Prepackaged

If I were to do this in a script or formula, I would do something like the following:

For category:

1) Select all records for the category, sort by date descending

2) For each product in the category, select the first record (most recent) 

3) Calculate the sum of the inventory across these most recent records

In the above example,

• The Category Total for Produce on the 1/10/2021 dated record would be 4 (Product 1) + 42 (Product 2) + 0 (Product 3) = 46. 

• On 1/12/2021 the number would change to 62 since the calculation would be 4 (Product 1) + 42 (Product 2) + 16 (Product 3) = 62.  The most recent record for Product C supercedes the original one at this point.

The same functionality would be applied at the subcategory level.  I'm just not sure how to dynamically select the most recent records within the category to perform the sum operation.  

Any suggestions? Many thanks for your advice.

[smpa01]

@Crefin  you need two measures for this

Measure1 = 
VAR _select =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _date =
    CALCULATE (
        MAX ( 'Table 1'[Date] ),
        FILTER ( 'Table 1', 'Table 1'[Date] <= _select )
    )
VAR _sum =
    CALCULATE (
        SUM ( 'Table 1'[Inventory] ),
        FILTER ( 'Table 1', 'Table 1'[Date] = _date )
    )
RETURN
    _sum




Measure2 = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table 1',
            'Table 1'[Category],
            'Table 1'[Subcategory],
            'Table 1'[Item]
        ),
        "total", [Measure1]
    ),
    [total]
)

[Crefin · Accepted solution]

Thank you! The table works perfectly. 

I will be using this calculation frequently across different metrics, specifically to perform weighted average calculations where the Measure2 value.  Would a calculated column be preferred? 

I tried converting it to a line chart to show the total over time and it seems to only show the most recent record as the "total"  (e.g. Produce shows 16 for January 22).

[Anonymous]

Did you, @Crefin, or @smpa01 ever figure out how to show the values over time via a line graph rather than for a single selected value? I'm running into the same issue?

[Crefin]

I ultimately wound up restructuring my data model to enable more out-of-the-box functionality to work.  In my case, it was to create a table containing a row for each applicable reporting date and columns showing each data point "as of" the given reporting date (which was many lookups for Max date <= report date that may be impractical depending on the volume of data).  The measures to calculate weighted averages and chart the output worked normally from there.  Good luck!

[Anonymous]

Thanks! For future reference: https://community.powerbi.com/t5/Desktop/Create-a-Line-Graph-showing-changes-to-a-portfolio-over-tim...

Just paying it forward if anyone else comes here. 

[amitchandak]

@Crefin , Create two measures

Last Inv = CALCULATE(LASTNONBLANKVALUE(Data[Date], calculate(SUM(Data[Inventory]))), FILTER(ALLSELECTED(Data), Data[Category] = max(Data[Category]) && Data[item] = MAX(Data[item])))

Total Inv = var _t = SUMMARIZE((Data), Data[Item],Data[Category]) var _tab = ADDCOLUMNS(_t,"_1",[Last Inv]) 
return sumx(_tab,[_1])

Use Total Inv

[Crefin]

Hi amitchandak,

thanks for your suggestion.  I was unable to produce the correct totals with this on all the dates.  On January 8 it correctly shows Produce inventory of 50.  Incrementing by 1 day, January 9 displays Produce inventory as blank.  Then, on the 10th when Fruit changes from 8 to 4 (which should result in inventory of 46), it shows inventory of 4, ignoring Item 2's inventory of 42 from January 8th.  It seems to identify the highest dated record per the slicer and sum the inventory records on that day.  In the business case here, the subcategories would not necessarily have records all on the same day.