Sum of quantities between the first date and a given date

[camillasperanza]

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

[v-shex-msft · Accepted solution]

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

[Greg_Deckler]

Really helps to have example data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490