I need to get a cumulative and rolling monthly total

[faronicus]

Hello,

Let's say I have this table:

Item	Period	Quantity
122	202406	-2.000
122	202405	-18.000
122	202404	44.000
122	202403	-24.000
122	202402	-2.000
122	202401	-1.000
122	202312	-12.000
118	202406	1.000
118	202405	2.000
118	202404	-1.000
118	202403	10.000
118	202402	-3.000
118	202401	8.000
118	202312	4.000

I'd like to get a cumulative, rolling total of the Quantity per item each month from the beginning of time. So in the end I'd like to get this new column:

Item	Period	Quantity	Rolling MTD
122	202406	-2.000	-15
122	202405	-18.000	-13
122	202404	44.000	5
122	202403	-24.000	-39
122	202402	-2.000	-15
122	202401	-1.000	-13
122	202312	-12.000	-12
118	202406	1.000	21
118	202405	2.000	20
118	202404	-1.000	18
118	202403	10.000	19
118	202402	-3.000	9
118	202401	8.000	12
118	202312	4.000	4

Thanks for any assistance!

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Samarth_18]

Hi @faronicus ,

You can also achieve this using the below measure code:-

Measure = 
VAR CurrentItem = SELECTEDVALUE('Table'[Item])
VAR CurrentPeriod = SELECTEDVALUE('Table'[Period])
RETURN
    CALCULATE (
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Item] = CurrentItem && 'Table'[Period] <= CurrentPeriod
            ),
            'Table'[Quantity]
        )
    )

[v-yilong-msft · Accepted solution]

Hi @faronicus ,

I create a table as you mentioned.

Then I create a Calculated Column and here is the DAX code.

Column = 
CALCULATE (
    SUM ( 'T2'[Quantity] ),
    FILTER (
        ALL ( 'T2' ),
        'T2'[Item] = EARLIER ( 'T2'[Item] )
            && 'T2'[Period] <= EARLIER ( 'T2'[Period] )
    )
)

Finally you will get what you want.

Best Regards

Yilong Zhou

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

[faronicus]

Thank you, Yilong. And also thanks for cleaning up my table's formatting!

[AllisonKennedy]

@faronicus  do you have a star schema setup yet? Your life will be much easier if you create a dimension table for Item and for Date, then you can use the time intelligence functions to get the rolling totals, and use the Item dimension table to filter it per item.

You definitely will want to create a date dimension for sure: 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

If you don't have a star schema yet, here's the last blog in my relationships series, with links to the first two: 

https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.html