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

Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.

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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors