Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, everyone
I need to add up all inventory for an item over the entire period by date. There are cases where the date and time are the same but occur one after the other. Here is my formula:
accumulated Stock =
calculate(
sumX('Goods logistics','Goods logistics'[Stock]),
FILTER(All('Goods logistics'),
And('Goods logistics'[ArtID]=EARLIER('Goods logistics'[ArtID]),
'Goods logistics'[Date]<=EARLIER('Goods logistics'[Date])
)))
How can I add, if the date is the same as the date, the smaller ID should also be preferred.
ArtID | Date_Time | ID | Stock | accumulated Stock | ||||
123 | 01.01.2020 02:20:10 | 220 | 3 |
| 3 | |||
123 | 02.01.2020 04:10:10 | 221 | -1 | 1 <- Should be 2 | ||||
123 | 02.01.2020 04:10:10 | 222 | -1 | 1 <-is correct | ||||
123 | 06.01.2020 01:13:21 | 225 | 2 | 3 |
Thank you for help.
Solved! Go to Solution.
@azaterol Then another option is add index from power query and then
accumulated Stock =
calculate(
sum('Goods logistics'[Stock]),
FILTER(ALL('Goods logistics'),
('Goods logistics'[ArtID]=EARLIER('Goods logistics'[ArtID]) &&
'Goods logistics'[INDEX] <= EARLIER('Goods logistics'[INDEX])
)))
@azaterol First make sure that ID column should be in ascending order
Then create calculated column as per below
accumulated Stock =
calculate(
sum('Goods logistics'[Stock]),
FILTER(ALL('Goods logistics'),
('Goods logistics'[ArtID]=EARLIER('Goods logistics'[ArtID]) &&
'Goods logistics'[ID] <= EARLIER('Goods logistics'[ID])
)))
@ddpl the date is priotity.
I changed the sample. If there are two or more same dates, the ID is in ascending order. I have to use Date_Time in my formula.
ArtID | Date_Time | ID | Stock | accumulated Stock | ||||
123 | 01.01.2020 02:20:10 | 229 | 3 |
| 3 | |||
123 | 02.01.2020 04:10:10 | 221 | -1 | 1 <- Should be 2 | ||||
123 | 02.01.2020 04:10:10 | 222 | -1 | 1 <-is correct | ||||
123 | 06.01.2020 01:13:21 | 220 | 2 | 3 |
@azaterol Then another option is add index from power query and then
accumulated Stock =
calculate(
sum('Goods logistics'[Stock]),
FILTER(ALL('Goods logistics'),
('Goods logistics'[ArtID]=EARLIER('Goods logistics'[ArtID]) &&
'Goods logistics'[INDEX] <= EARLIER('Goods logistics'[INDEX])
)))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |