Rolling Totals to date with last available aggregate for days/weeks/months with no values

[Anonymous]

Hi, community!

I have an apparently very easy problem to solve, but I just don't seem to get it  right no matter what I do.

Getting into the problem, I have various products (let's call them SKUs) grouped by batches (called IO). I want to see the evolution of the inventory value of said SKUs, but, and here comes the real cause of complexity, the variations generally don't occur on the same dates, but I need to know what is the last value of my inventory for each day, even when they inventory value is the same as the day before.

For example, I have this referential facts table with the movements in my inventory

Date	Main IO	IO	Product	Plot	Batch Description	USD (Thousands)	Motive
01/01/2022	1888	1888-L-1	SKU-1	1	SKU-1.1.22	10	IO opening
07/01/2022	1999	1999-L-2	SKU-2	2	SKU-2.2.22	25	IO opening
15/02/2022	1888	1888-L-1	SKU-1	1	SKU-1.1.22	-0.5	Exchange Rate Adjustement
13/08/2022	1999	1999-L-2	SKU-2	2	SKU-2.2.22	0.84	Exchange Rate Adjustement
21/10/2022	1999	1999-L-2	SKU-2	2	SKU-2.2.22	-0.34	Defective Products
21/12/2022	1888	1888-L-1	SKU-1	1	SKU-1.1.22	-0.75	Defective Products
07/01/2023	1999	1999-L-2	SKU-2	2	SKU-2.2.22	-0.75	Environment Damage
07/03/2023	1888	1888-L-1	SKU-1	1	SKU-1.1.22	-0.38	Environment Damage
18/11/2023	1888	1888-L-1	SKU-1	1	SKU-1.1.22	-0.37	Pre-Holidays Adjustment
18/11/2023	1999	1999-L-2	SKU-2	2	SKU-2.2.22	-1.3	Pre-Holidays Adjustment
08/01/2024	1999	1999-L-2	SKU-2	2	SKU-2.2.22	-23.45	Holidays Liquidation

My goal is to create a visual and a matrix that show me the evolution of the inventory value through time for each product, but when I try to create my visuals, the results are not what I expected.

For the line chart and table, my goal is this:

But when I create my measure, it returns me this instead:

My measure is this (I've tried a lot of other, more complex, measures found on the internet, but the results are basically the same) :

agg_measure = 
VAR _max = MAX(Table1[Date])
VAR _calc = CALCULATE(
                    SUMX(Table1,
                               Table1[USD (Thousands)]),
                               ALL('Calendar'),
                               Table1[Date]<=_max
                         )
//return IF(_calc<>0,_calc,BLANK())
return _calc

Ideally, I would prefer for the graph not to show 0 values (when the batch is sold out), but the line to simply stop appearing.

And I would also love for the report to automatically show me the inventory value up to the present day, I suspect I only need to replace the VAR _max for a VAR _today=TODAY(), but I have no way to try it and evaluate the result.

So, that's it. I don't know if I can or have to share the referential .xlsx, but if you need it, I'm more than happy to share it if it helps you help me. Thank you for your time.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

I suggest you to create an unrelated DimDate table to help calcualtion.

DimDate = VALUES('Table'[Date])

Measure:

SKU-1 = CALCULATE(SUM('Table'[USD (Thousands)]),FILTER('Table','Table'[Product] = "SKU-1" && 'Table'[Date]<= MAX(DimDate[Date])))

SKU-2 = CALCULATE(SUM('Table'[USD (Thousands)]),FILTER('Table','Table'[Product] = "SKU-2" && 'Table'[Date]<= MAX(DimDate[Date])))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Based on what I got.
Make sure date from date table is used in visual, Try like

agg_measure =
VAR _max = MAX(Table1[Date])
VAR _calc = CALCULATE(
SUMX(Table1,
Table1[USD (Thousands)]),
filter(ALL('Calendar'),'Calendar'[Date] <= max('Calendar'[Date])
'Calendar'[Date]<=_max
))
//return IF(_calc<>0,_calc,BLANK())
return _calc