Calculating Rolling Sum 12 Month Inventory Balance

[sofigs]

My end goal is to calculate rolling 12 month inventory average. I'm having problems calculating the rolling 12 month sum of my inventory balance due to the format of my data source.

In January of every year, I have a starting inventory balance and on the following months I have only the inventory movements. (Highlighted in green are the inventory balances for years 2016 and 2017).

I was able to calculate the inventory balance for each month using the YTD formula

YTD Inventory = CALCULATE([Total Inventory], DATESYTD('Calendar'[Date]))

But when I try to use that measure to calculate the rolling 12 month sum, it doesn't work because it restarts at the beginning of each year and it's just adding the movements, not the inventory balance.

I also tried with the Total Inventory measure but it doesn't work

Total Inventory = SUM(Inventory[Inventory])

This is what I want,

and this is what I currently have

Here is my workbook with the sample data.

I would appreciate any help and guidance on what to do next.

Thanks,

Sofi

[Greg_Deckler]

There is a rolling average Quick Measure, have you looked at that?

[sofigs]

Just tried it but can't use Quick Time Intelligence measure because I'm using an extrenal Calendar Table.

But I think it would have the same problem as I currently have. It would average the inventory movements not the inventory monthly balance.

I think I need to save the final monthly inventory balance somewhere so I can use my rolling sum measure on that result, but I have no idea how to go about it.

[Vvelarde · Accepted solution]

@sofigs 

Hi, try with this:

Step 1: A measure:

SumInventoryThisYear =
VAR _Fecha =
    SELECTEDVALUE ( Table1[Fecha] )
RETURN
    CALCULATE (
        SUM ( Table1[Inventario] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Fecha] <= _Fecha
                && YEAR ( Table1[Fecha] ) = YEAR ( _Fecha )
        )
    )

2. Rolling Measure:

Rolling =
VAR _Fecha =
    SELECTEDVALUE ( Table1[Fecha] )
VAR _Fecha_12MonthsBefore =
    EDATE ( SELECTEDVALUE ( Table1[Fecha] ), -12 )
VAR _Rolling =
    SUMX (
        FILTER (
            ALL ( Table1 ),
            Table1[Fecha] <= _Fecha
                && Table1[Fecha] > _Fecha_12MonthsBefore
        ),
        [SumInventoryThisYear]
    )
RETURN
    _Rolling

Regards

Victor

[sofigs]

Thank you Victor! That worked perfectly 🙂