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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Zalexatwork
Frequent Visitor

Cumulative sum with category filter and initial number

Hi,

Quite new to power Bi so I apologise if my question has been posted (I have checked but could not apply any of the proposed answers on similar topics).
I have a table that has 4 columns : 


             Date                  City         Inital inventory      inventory movement                             Wanted column:
row 1    01/01/2021      A             10                            null                                                                   10
row 2    01/01/2021      B             8                              null                                                                    8

row 3    01/01/2021      C             5                              null                                                                    5

row 4    02/01/2021      A             null                            2                                                                     12

row 5    02/01/2021      B             null                            1                                                                      9
row 6    02/01/2021      C             null                            3                                                                      8
row 7    02/01/2021      A             null                            2                                                                     14

...

and so on

 

So three initialisation rows on an arbitrarily fixed day (created a manual table and chose a day where I was sure of the stock value of said day), and then one date per city, so every date appears three times, one for each city.

What I would like to do is add a column that can, for each date after my initialisation date, give me the total stock by adding initial inventory + all movements from previous days, while filtering on the city (the 'wanted column' from above).
The answers I have read either
- did not have the initialisation that I require (actual data goes way back and there have been losses in terms of per day movement so I cant just do a cumulative sum from the first date that exists in the data), 
 - Or did not take into account the filter I need on the cities 

Any help would be greatly appreciated!


1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@Zalexatwork 

You can add a column as follows and make sure you have Row or index column:

Cummulative = 
var __row = Table3[Row]
var __city = Table3[City]
return
CALCULATE(
    SUM(Table3[Initial]) + SUM(Table3[Movement]),
    Table3[Row] <= __row,
    Table3[City] = __city,
    REMOVEFILTERS()
    
)

 

Fowmy_0-1628604643273.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Zalexatwork
Frequent Visitor

Thank you for a fast response! 
This does work. However it also means that if I change my initialisation date, I have to manually change filters in the query to make sure index column starts on the new initialisation date.
Not a need that I necessarily have right now but i would feel safer, for maintenance reasons or use by other colleagues, if i could filter directly in the sum formula (equivalent of a Sumhviser + if condition in excel), if that is possible.
Thank you though, I will use the above technique for now!

Fowmy
Super User
Super User

@Zalexatwork 

You can add a column as follows and make sure you have Row or index column:

Cummulative = 
var __row = Table3[Row]
var __city = Table3[City]
return
CALCULATE(
    SUM(Table3[Initial]) + SUM(Table3[Movement]),
    Table3[Row] <= __row,
    Table3[City] = __city,
    REMOVEFILTERS()
    
)

 

Fowmy_0-1628604643273.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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

Top Solution Authors