cancel
Showing results for
Did you mean:
New Member

## Sum of quantities between the first date and a given date

Hello,

i'm pretty new with DAX language.

I have two databases, A and B

A gives me the code of the item and the "receipt dates" and related quantities in that date;

B gives me the same codes of items and a column with the date.

I have to calculate the sum of quantities from the first receipt date until the "date" given for that specific item.

Consider that:

- in database A the item code can be repeated for more than one date (that's why i want the sum of those dates until the date specified in database B

- th items code are not in the same order among the two databases and in database B there could be as well the code repeated in different dates.

Any suggestion?

thank you very much

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Sum of quantities between the first date and a given date

Hi @camillasperanza ,

Please provide more detail informations to help us clarify your data structure to test and coding formula.

You can also try to use following measure if it works for your scenario:

```Measure =
VAR selectedItem =
ALLSELECTED ( TableB[Code] )
VAR selectedDate =
MAX ( Date[Date] )
VAR _dateList =
CALCULATETABLE (
VALUES ( TableA[Receipt Date] ),
FILTER (
ALLSELECTED ( TableA ),
[Code] IN selectedItem
&& [Receipt Date] <= selectedDate
)
)
RETURN
CALCULATE (
SUM ( Table[Quantity] ),
FILTER ( ALLSELECTED ( Table ), [Date] IN _dateList )
)
```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
2 REPLIES 2
Super User

## Re: Sum of quantities between the first date and a given date

Proud to be a Datanaut!

Community Support Team

## Re: Sum of quantities between the first date and a given date

Hi @camillasperanza ,

Please provide more detail informations to help us clarify your data structure to test and coding formula.

You can also try to use following measure if it works for your scenario:

```Measure =
VAR selectedItem =
ALLSELECTED ( TableB[Code] )
VAR selectedDate =
MAX ( Date[Date] )
VAR _dateList =
CALCULATETABLE (
VALUES ( TableA[Receipt Date] ),
FILTER (
ALLSELECTED ( TableA ),
[Code] IN selectedItem
&& [Receipt Date] <= selectedDate
)
)
RETURN
CALCULATE (
SUM ( Table[Quantity] ),
FILTER ( ALLSELECTED ( Table ), [Date] IN _dateList )
)
```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |