Calculating 12 Month Running Totals from a Cumulative Total Measure

[jortega8809]

Hi!

I am trying calculate a rolling 12 month total from a cumulative sum measure.

I am sure it's an easy fix but every solution I have searched for on the forums and on google have led me to the same error.

Here is my Cumulative Measure.

Inv Value Total =
CALCULATE (
    SUM ( 'Inventory'[_CostCombined] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    ),
    'Inventory'[Item No_] <> BLANK ()
)

Basically I am trying to gather a rolling 12 Month Cost at Monthend and getting it's average to calculate inventory Turns.

Rolling 12 Months of CoGS / (Total Rolling 12 Months Inventory Value at Monthend/12)

[v-yuta-msft]

Hi jortega8809,

Could you provide sample data of your tables and clarify relationship betweeb these tables? For example, in your screenshot I haven't found 'Inventory'[Item No_], 'Inventory'[_CostCombined] and your calendar table. Could you give more details about them?

Regards,

Jimmy Tao

[jortega8809]

Thank you so much for the reply @v-yuta-msft

[Item No_] is a variable that I incorporated to remove Nulls, so in this case it is not important.

I am bad at explaining things so I made a small sample for anyone that is interested in assisting.

'Calendar'[Date] 1>*'Value Entry'[Posting Date]

'Value Entry'[Inventory Value] is data from the Value Entry Table

[1 Cumulative Inventory Value] is a measure I created, as Inventory Values should be calculated cumulatively.

1 Cummulative Inventory Value =
CALCULATE (
    SUM ( 'Value Entry'[Inventory Value] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

I am trying to create a measure [Rolling 12Mth Avg] that will calculate the 12 month average of  [1 Cumulative Inventory Value]

which would take us back to the example above.

For example on the row with May 2018 in the model I want the measure to sum cummulative totals for their respective months

from June 2017-May 2018 and then take the average (which would mean to divide the total sum by 12)

Here is a dropbox link for the file.

https://www.dropbox.com/s/vrrjv4i07pcidaq/12%20Month%20Rolling%20Total%20Example.pbix?dl=0

Thanks so much for your time. I hope this clarifies things.

[jortega8809]

Still hoping for a bit of help 😞

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this file.  Hope this helps.

[aralcocer]

Hello, This seems to be what I need for a project but the file is no longer accessible. Could you repost it or send me the PBIX?
Thank you in advance!

[Ashish_Mathur]

It is an old post.  I do not have the file.  Share some data to work with, explain the question and show the expected result.

[jortega8809]

@Ashish_MathurThank you so much! This is the solution I was looking for 🙂

[Ashish_Mathur]

You are welcome.

[v-yuta-msft]

Hi jortega8809,

Based on your description, table Inventory and table Calendar has a relationship based on column [date], right?  If so, you can modify you measure like this pattern and check if it meet your requirement:

Inv Value Total =
VAR Previous_Month =
    MAX ( 'Calendar'[Date] ) - 12
VAR Current_Month =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Inventory'[_CostCombined] ) / 12,
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= Current_Month
                && 'Calendar'[Date] >= Previous_Month
                && 'Inventory'[Item No_] <> BLANK ()
        )
    )

Regards,

Jimmy Tao

[jortega8809]

Thank you for your time @v-yuta-msft

This is the caculation from the first post

it's calculated correctly as it is a Cumulative Total.

What I am trying to do is gather an average for a  12 Month Rolling total based on a Cumulative Total from this measure. 

I don't know if the solution is creating a measure based from this [Inv Value Total] Measure.

I tried your Solution and unfortunately it did not perform the rolling 12 month Sale that i am trying to achieve 😞

Here is an excel example of what I am trying to achieve

Thanks in advance for any help!

[jortega8809]

Hoping for a nudge in the right direction.

🙂