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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.