Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
@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()
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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!
@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()
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Check out the July 2025 Power BI update to learn about new features.