cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sofigs Frequent Visitor
Frequent Visitor

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
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: Calculating Rolling Sum 12 Month Inventory Balance

@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

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
Super User
Super User

Re: Calculating Rolling Sum 12 Month Inventory Balance

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

sofigs Frequent Visitor
Frequent Visitor

Re: Calculating Rolling Sum 12 Month Inventory Balance

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 Super Contributor
Super Contributor

Re: Calculating Rolling Sum 12 Month Inventory Balance

@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

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

sofigs Frequent Visitor
Frequent Visitor

Re: Calculating Rolling Sum 12 Month Inventory Balance

Thank you Victor! That worked perfectly 🙂

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 152 members 1,699 guests
Please welcome our newest community members: