PowerBI DAX measure to sum over most recent values per category

Question

I have a table in PowerBI which holds the inventory levels of different categories at various dates. Example data:

InventoryTable

Category	Date	Inventory
A	31.10.2023	15
E	31.10.2023	61
A	31.12.2023	1
B	31.12.2023	13
A	31.01.2024	39
B	31.01.2024	72
B	29.02.2024	38
A	31.03.2024	46

More readable, the InventoryTable looks like this

Category	31.10.2023	31.12.2023	31.01.2024	29.02.2024	31.03.2024
A	15	1	39		46
B		13	72	38
E	61

A DAX measure "Latest Inventory" should now take the most recent values from the selected time slice (or earlier) per category and sum them up over the categories.

Expected output:

Latest Inventory

Category	2023	2024	Total
A	1	46	46
B	13	38	38
E	61	61	61
Total	75	145	145

drilled down into month, this should look like

Latest Inventory

Category	October	November	December	2023 Total	January	February	March	2024 Total	Total
A	15	15	1	1	39	39	46	46	46
B			13	13	72	38	38	38	38
E	61	61	61	61	61	61	61	61	61
Total	76	76	75	75	172	138	145	145	145

I absolutely cannot get my head around this. Most approaches either fail because different categories have different record dates or the split between the categories is not reflected in the totals. Can it be that hard?

edit 1:

Thinking more about it, I assume that I need to bring a DateTable into the model and align my inventory records along the Date column. Similar to what one would do to accumulate values over time - just that in this case, I do not need to sum up over time but only take the most recent value.

I just don't understand how to do that.

edit 2:

I might be closer to a solution by now. This is what I have:

Latest Inventory = CALCULATE(
    LASTNONBLANKVALUE('InventoryTable'[Date], SUM('InventoryTable'[Inventory])),
    DateTable[Date] <= MAX(DateTable[Date]),
    ALL(DateTable[Date])
)

It does calculate the correct values per category. But if I sum up over the categories, the total only has the sum of values that have been recorded at the most current date of ALL categories. If one category has been recorded earlier, it's not contained in the Total.

Already tried to SUMX over the VALUES('InventoryTable'[Category]), but this messes up the whole thing.

Answer 1

In combination with a pivot table this should work for your monthly and yearly data

MostRecentMonthlyInventory = 
VAR CurrentCategory = SELECTEDVALUE('Table'[Category])
VAR CurrentYear = YEAR(MAX('Table'[Date]))
VAR CurrentMonth = MONTH(MAX('Table'[Date]))
RETURN
CALCULATE(
    MAX('Table'[Inventory]),
    FILTER(
        ALL('Table'),
        'Table'[Category] = CurrentCategory &&
        YEAR('Table'[Date]) = CurrentYear &&
        MONTH('Table'[Date]) = CurrentMonth &&
        'Table'[Date] = CALCULATE(MAX('Table'[Date]),
            ALLEXCEPT('Table', 'Table'[Category])
        )
    )
)

I wrote this code first then changed my mind about the best way to do this. to show totals for 23 and most recent for 24 an have included it because

MostRecentInventory2023 = 
CALCULATE(
    MAX('Table'[Inventory]),
    FILTER(
        ALL('Table'),
        'Table'[Date] = CALCULATE(
            MAX('Table'[Date]),
            FILTER(
                ALL('Table'),
                YEAR('Table'[Date]) = 2023 && 
                'Table'[Category] = EARLIER('Table'[Category])
            )
        )
    )
)

MostRecentInventory2024 = 
CALCULATE(
    MAX('Table'[Inventory]),
    FILTER(
        ALL('Table'),
        'Table'[Date] = CALCULATE(
            MAX('Table'[Date]),
            FILTER(
                ALL('Table'),
                YEAR('Table'[Date]) = 2024 && 
                'Table'[Category] = EARLIER('Table'[Category])
            )
        )
    )
)