Forum Discussion

sofigs's avatar
sofigs
Frequent Visitor
7 years ago
Solved

Calculating Rolling Sum 12 Month Inventory Balance

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).

source_data

 

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,
wanted result
 
and this is what I currently have
incorrect calculations
 
Here is my workbook with the sample data.
 
I would appreciate any help and guidance on what to do next.
 
 
Thanks,
Sofi
 
 
Need Help
  • Vvelarde's avatar
    Vvelarde
    7 years ago

    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

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

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

    • sofigs's avatar
      sofigs
      Frequent Visitor
      7 years ago

      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's avatar
        Vvelarde
        Community Champion
        7 years ago

        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