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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
azaterol
Helper V
Helper V

AND condition help

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.

1 ACCEPTED 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])

)))

 

View solution in original post

4 REPLIES 4
ddpl
Solution Sage
Solution Sage

@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])

)))

 

@ddpl works great. Thank you. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.