cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
camillasperanza New Member
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
Highlighted
Community Support Team
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
Super User

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

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
Community Support Team
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: | |