cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Solution Sage

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

4 REPLIES 4
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]))))`

Helper V

@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

Solution Sage

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

Helper V

@ddpl works great. Thank you.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors