Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
faronicus
Regular Visitor

I need to get a cumulative and rolling monthly total

Hello,

 

Let's say I have this table:

 

ItemPeriodQuantity
122202406-2.000
122202405-18.000
12220240444.000
122202403-24.000
122202402-2.000
122202401-1.000
122202312-12.000
1182024061.000
1182024052.000
118202404-1.000
11820240310.000
118202402-3.000
1182024018.000
1182023124.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:

ItemPeriodQuantityRolling MTD
122202406-2.000-15
122202405-18.000-13
12220240444.0005
122202403-24.000-39
122202402-2.000-15
122202401-1.000-13
122202312-12.000-12
1182024061.00021
1182024052.00020
118202404-1.00018
11820240310.00019
118202402-3.0009
1182024018.00012
1182023124.0004



Thanks for any assistance!

1 ACCEPTED SOLUTION
v-yilong-msft
Community Support
Community Support

Hi @faronicus ,

I create a table as you mentioned.

vyilongmsft_0-1720763132068.png

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.

vyilongmsft_1-1720763244863.png

 

 

 

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.

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1721012476457.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Samarth_18
Community Champion
Community Champion

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

 

Samarth_18_0-1720764651126.png

 

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

v-yilong-msft
Community Support
Community Support

Hi @faronicus ,

I create a table as you mentioned.

vyilongmsft_0-1720763132068.png

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.

vyilongmsft_1-1720763244863.png

 

 

 

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.

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

AllisonKennedy
Super User
Super User

@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 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Users online (2,787)